Five SQL Tips from Chris Fehily
Like this article? We recommend
Like this article? We recommend
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.