Skip to main content

System Status: 

How to Get Better SQL Results

These tips can help Data Warehouse users create SQL statements that yield the desired results.

Note: If you don't know the SQL programming language, you can use QueryLink by clicking the Queries button in FinancialLink, EmployeeLink, Student/ Class Info tools, and DataLink.

Perform your query on a small data sample.

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.

Recheck queries containing join statements.

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

Recheck queries containing subqueries.

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.

Include a group by clause when you use an aggregate function such as sum or count.

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

Be aware of restrictions using the group by clause.

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.

Check your use of parentheses when writing queries that contain logical connectives such as and, or, and not.

Sometimes when you mix and and or, SQL does not process the expression in the order you expect. Use parentheses in complex expressions to ensure the result you get is the one you want. The few extra keystrokes will show better quality results.

Contact the Data Warehouse team. For detailed instructions on SQL, enroll in a SQL training class.