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
Requires Free Membership to View
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.
This was first published in November 2001
Join the conversationComment
Share
Comments
Results
Contribute to the conversation