|
Excellent question, because it's difficult to
come up with a wrong answer. ? ;o)
1. Think in sets of rows, not individual rows
This is a subtle distinction. "Who are the employees
that have the lowest salary in their department?" is
slightly different than "For each department, find the
employees that have the lowest salary." The former makes
us look at the problem in terms of which rows satisfy
the requirement, while the latter diverts our
attention to the procedure for finding them.
select dept, employee, salary
from payroll XX
where salary = ( select min(salary)
from payroll
where dept = XX.dept )
You don't have to write any code to "loop through" the
rows. Just describe which set of rows you want.
2. There is no "how"
Waste no time worrying about how efficient your SQL is.
Concentrate on the sets you want your SQL to operate on.
Which is better, a join or a subselect? Use whichever you
are comfortable with, as long as it is semantically
correct, i.e. operates on the right set
of rows. For example, what is the average salary
of employees on staff less than one year?
You could do it like this --
select avg(salary)
from payroll
where employeeid in
( select employeeid
from employees
where hiredate > current_date() - 1 year )
or like this --
select avg(salary)
from payroll
inner join employees
on employeeid
where hiredate > current_date() - 1 year
Both queries give the same results.
3. Design to third normal form
This has actually nothing to do with the
SQL language at all. Make sure your
rowboat is watertight and you won't
be constantly bailing. Design your
tables to third normal form
and your SQL becomes very, very simple.
Tip: Despite my advice
in 2. above not to worry about how
efficient your SQL is, it is still a very good idea to
make sure your primary keys have indexes.
Possibly your foreign keys as well.
For More Information
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
|
