Publishers of technology books, eBooks, and videos for creative people

Home > Articles

Five SQL Tips from Chris Fehily

  • Print
  • + Share This
Like this article? We recommend

Avoid division by zero with NULLIF

Suppose you want to calculate the male–female ratios for various school clubs, but you discover that the following query fails and issues a divide-by-zero error when it tries to calculate ratio for the Lord of the Rings Club, which has no women:

SELECT club_id, males, females, males/females AS ratio
  FROM school_clubs;

You can use the function NULLIF to avoid division by zero. NULLIF compares two expressions and returns null if they are equal or the first expression otherwise. Rewrite the query as:

SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio
  FROM school_clubs;

Any number divided by NULL gives NULL, and no error is generated.


Ordering values for IN expressions

To speed the evaluation of IN expressions, list the most likely values first.

If you're testing U.S. addresses, for example, list the most populous states first:

WHERE state IN ('CA', 'TX', 'NY', 'FL',...,'VT', 'DC', 'WY')


Specifying endpoints for BETWEEN expressions

Specifying a character range for a BETWEEN expression often needs some thought.

Suppose you want to search for last names that begin with the letter F. The following clause won't work because it will retrieve someone whose last name is the letter G (is the letter G, not starts with the letter G):

WHERE last_name BETWEEN 'F' AND 'G'

This next clause shows the correct way to specify the ending point (in most cases):

WHERE last_name BETWEEN 'F' AND 'Fz'


Confusing AND and OR

It's easy to translate a correctly phrased spoken-language statement into an incorrect SQL statement.

If you say, "List the books priced less than $10 and more than $30," the and suggests the use of the AND operator:

SELECT title_name, price
  FROM titles   WHERE price < 10 AND price > 30
--Wrong

This query returns no rows, however, because it's impossible for a book to be priced less than $10 and more than $30 simultaneously, as AND logic commands. The logical meaning of OR finds books that meet any of the criteria, not all the criteria at the same time:

WHERE price < 10 OR price > 30; --Correct


Simplifying comparisons

For speedier comparisons, fold your constants into a minimal number of expressions. For example, change

WHERE col1 + col2 <= 10 to WHERE col1 + col2 <= 8

Try to put only simple column references to the left of the comparison operator, and more complex expressions to the right. In general, the fastest comparison is for equality (=), following by the inequalities (<, <=, >, >=). The slowest is not-equal (<>). If possible, express conditions by using faster comparisons.

  • + Share This
  • 🔖 Save To Your Account