How to Get Better SQL Results

Run the query on test data you know and see if the result matches your expectations. If it doesn't, you may need to reformulate your query.

If your query contains a join, make sure it is doing what you expect before you add where clauses or other complicating factors.

Select statements that contain subqueries can entangle data taken from one table with data taken from another. Make sure that the data the inner select retrieves is the data that the outer select needs to produce the desired end result. If you have two or more levels of subqueries, you must be even more careful.

If you have a table called (national) containing column names called player, team and homers (number of home runs hit by every baseball player in the National League), you can retrieve the home run total for all teams with a query like this:

• select team, sum (homers)
  from national
  group by team

Suppose you want to produce a list of power hitters in the National League. Consider this query:

• select player, team, homers
  from national
  where homers >= 20
  group by team

In most implementations, this query returns an error. Generally, only columns used for grouping, or columns used in an aggregate function, may appear in the select list.

This query works better:

• select player, team, homers
  from national
  where homers >= 20
  group by team, player, homers

Since all the columns you want to display appear in the group by clause, the query succeeds and delivers the results you want. This formulation sorts the resulting list first by team, then by player, and finally by homers.