Key factors for successful SQL

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

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.