Special Operators and Clauses

The operators and clauses that make filtering and presenting results practical. IN and the NOT IN trap, in which one NULL in a list returns no rows and a natural query about non-smokers loses 3,497 never-smokers; BETWEEN and the closed intervals that count 33 people in two BMI classes at once; LIKE, GLOB and ESCAPE on drug names, where a search for statins also finds an antifungal; CASE, COALESCE and NULLIF for recoding survey answers; DISTINCT and how it treats NULL; ORDER BY with NULLs and ties; and LIMIT, OFFSET and the dialects' versions of top-N, where 427 people tie for oldest. On the NHANES 2017-2018 database in SQLite.

NHANES asks adults two questions about smoking: whether they have smoked at least 100 cigarettes in their life (smq020: 1 yes, 2 no) and, if so, whether they smoke now (smq040: 1 every day, 2 some days, 3 not at all). Someone who has never smoked 100 cigarettes is not asked the second question, so their smq040 is NULL. A query for the people who do not currently smoke is easy to write:

SELECT COUNT(*) FROM smoking WHERE smq040 NOT IN (1, 2);   -- 1,338

It returns the 1,338 former smokers and none of the 3,497 people who never smoked, because for them smq040 NOT IN (1, 2) is unknown, not true. The query is syntactically fine and the answer is wrong by a factor of more than three.

This article covers the operators that go beyond the comparisons of Simple SQL Queries, IN, BETWEEN, LIKE and the conditional expressions CASE, COALESCE and NULLIF, and the clauses that shape a result: DISTINCT, ORDER BY and LIMIT. Several of them interact with NULL in ways that change answers, and several differ between SQL dialects; both are pointed out as they arise. The examples run on the NHANES database described in Relational Databases.

IN and NOT IN

x IN (a, b, c) is true when x equals any value in the list; it is shorthand for x = a OR x = b OR x = c. The NHANES race and Hispanic origin codes 1 and 2 are Mexican American and other Hispanic, so

SELECT COUNT(*) FROM person WHERE race IN (1, 2);   -- 2,187

counts the 2,187 Hispanic participants, the same as the longer race = 1 OR race = 2. The list can also be a subquery that produces the values, WHERE seqn IN (SELECT seqn FROM ...), covered in Subqueries.

NOT IN negates the test, and inherits the three-valued logic of the comparisons behind it. When x is NULL, every comparison x = a is unknown, so x IN (...) and x NOT IN (...) are both unknown and the row is dropped either way. That is what happened to the never-smokers above. The repair is to say what should happen to them:

SELECT COUNT(*) FROM smoking
WHERE smq040 NOT IN (1, 2) OR smq040 IS NULL;   -- 5,703

This returns 5,703 people: 1,338 former smokers, 3,497 never-smokers and 868 adolescents aged 12 to 17, who were not asked the adult questions at all (their smq020 is also NULL). Whether those 868 belong in the answer is again a decision; a query that classifies people from both questions, with CASE below, makes the decision visible.

A NULL inside the list is worse. For a value not in the list, x NOT IN (1, 2, NULL) means x <> 1 AND x <> 2 AND x <> NULL, and the last term is unknown, so the whole condition can never be true:

SELECT COUNT(*) FROM smoking WHERE smq040 NOT IN (1, 2, NULL);   -- 0

IN with a NULL in the list still finds the matches (1,021 current smokers for IN (1, 2, NULL)), because one true term makes an OR true. The SQLite documentation gives the full table of results for IN and NOT IN, which agrees with this reasoning (SQLite, n.d.-a). Nobody writes NULL into a literal list on purpose, but a subquery that returns a NULL among its values has the same effect, and Subqueries shows a real case in which NOT IN returns nothing while NOT EXISTS gives the right answer.

BETWEEN

x BETWEEN a AND b is equivalent to x >= a AND x <= b: both ends are included (SQLite, n.d.-a). age BETWEEN 18 AND 39 includes 18-year-olds and 39-year-olds, 1,974 people in all.

Closed intervals are natural for integers and a trap for measurements cut into classes. The usual BMI classes are overweight from 25 to under 30 and obesity class 1 from 30 to under 35. Written with BETWEEN,

SELECT COUNT(*) FROM exam WHERE bmi BETWEEN 25 AND 30;   -- 1,990
SELECT COUNT(*) FROM exam WHERE bmi BETWEEN 30 AND 35;   -- 1,289

the two classes share their boundary. BMI is recorded to one decimal, and 33 people have a BMI of exactly 30.0; they are counted in both classes, so the classes overlap and their counts cannot be added. Half-open intervals avoid this: bmi >= 25 AND bmi < 30 gives 1,957 and bmi >= 30 AND bmi < 35 gives 1,269, and every person falls in exactly one class. The same choice of closed side arises with pd.cut in pandas, discussed in Enriching Data.

Pattern matching: LIKE and GLOB

LIKE compares text with a pattern in which % matches any sequence of characters, including none, and _ matches exactly one character. Every other character matches itself. Drug names in NHANES are stored in capitals, so the statins, the cholesterol-lowering drugs whose generic names share an ending, can be found with:

SELECT drug_id, name FROM drug WHERE name LIKE '%STATIN' ORDER BY name;
drug_id name
d04105 ATORVASTATIN
d05348 EZETIMIBE; SIMVASTATIN
d03183 FLUVASTATIN
d00280 LOVASTATIN
d01233 NYSTATIN
d07637 PITAVASTATIN
d00348 PRAVASTATIN
d04851 ROSUVASTATIN
d00746 SIMVASTATIN

The pattern does what it says and not what was meant. Nystatin is an antifungal, not a statin; it matches because its name happens to end in the same letters. The combination product ezetimibe with simvastatin is a statin and is found only because simvastatin comes last in its name. Changing the pattern to '%STATIN%' adds two more products containing nystatin, NYSTATIN TOPICAL and NYSTATIN; TRIAMCINOLONE TOPICAL. Excluding the name NYSTATIN explicitly, '%STATIN' counts 1,217 prescriptions to 1,210 people. With '%STATIN%' the same exclusion gives 1,225 prescriptions to 1,216 people, and every one of the extra eight is one of the two topical nystatin products, which the exclusion of the exact name does not catch. Pattern matching on names is a quick first pass, and its results need reading; a reliable classification of drugs needs a table of drug classes keyed by drug identifier, which is a join, not a pattern.

The single-character wildcard is useful when the length of a code matters. ICD-10-CM codes have a three-character category, optionally followed by a dot and more characters. icd10 LIKE '___' finds the 92 reason codes that are bare categories, and icd10 LIKE 'E11._' finds the type 2 diabetes codes with exactly one character after the dot (E11.2, E11.4, E11.8 and the survey's own E11.P, "Prevent diabetes"), while 'E11%' also matches E11 itself and E11.2P.

To match a literal % or _, the pattern must say which character escapes it, with an ESCAPE clause: '100%' LIKE '100\%' ESCAPE '\' is true, and '1000' LIKE '100\%' ESCAPE '\' is false. SQLite, like standard SQL, has no default escape character. MySQL and PostgreSQL treat backslash as the escape character when no ESCAPE clause is given (MySQL, n.d.-a; PostgreSQL Global Development Group, n.d.-a), so a pattern containing a backslash can mean one thing there and another in SQLite.

Case sensitivity, and the dialects

Whether LIKE ignores case depends on the dialect. In SQLite, LIKE is case-insensitive for the letters of the ASCII alphabet only: 'atorvastatin' LIKE 'ATORVASTATIN' is true, but the documentation notes that 'æ' LIKE 'Æ' is false (SQLite, n.d.-a). In PostgreSQL, LIKE is case-sensitive and ILIKE is the case-insensitive version (PostgreSQL Global Development Group, n.d.-a). In MySQL the answer depends on the collation: comparisons ignore case unless an operand uses a case-sensitive collation or is a binary string, so 'abc' LIKE 'ABC' is true under the default collation (MySQL, n.d.-a). SQLite's GLOB operator matches case-sensitively with the Unix file wildcards * and ?: 'atorvastatin' GLOB 'ATORVASTATIN' is false and 'ATORVASTATIN' GLOB '*STATIN' true.

For patterns beyond these wildcards, the dialects provide regular expressions in different ways. The SQL standard defines SIMILAR TO, which PostgreSQL implements alongside its POSIX operator ~ (PostgreSQL Global Development Group, n.d.-a); MySQL has REGEXP. SQLite parses REGEXP but defines no function to carry it out, so it raises an error unless the application supplies one (SQLite, n.d.-a); from Python, sqlite3 can register a function built on the re module.

Conditional expressions

CASE

CASE chooses a value by conditions, like if ... elif ... else. It is the standard way to recode survey answers into categories. The two smoking questions combine into a three-category status:

SELECT CASE
         WHEN smq020 = 2 THEN 'never'
         WHEN smq020 = 1 AND smq040 = 3 THEN 'former'
         WHEN smq020 = 1 AND smq040 IN (1, 2) THEN 'current'
       END AS smoking_status,
       COUNT(*) AS people
FROM smoking
GROUP BY smoking_status
ORDER BY people DESC;
smoking_status people
never 3,497
former 1,338
current 1,021
NULL 868

The WHEN conditions are tried in order and the first true one wins. When none is true and there is no ELSE, the result is NULL, which is how the 868 adolescents who were not asked end up in a group of their own. Leaving out the ELSE here is deliberate: a missing answer stays visibly missing, instead of being filed under a category it does not belong to. (GROUP BY, which forms one row per status, is the subject of Aggregate Functions.)

The second form, CASE x WHEN v1 THEN r1 WHEN v2 THEN r2 ... END, compares one expression with a list of values and suits code tables:

SELECT CASE health
         WHEN 1 THEN 'excellent' WHEN 2 THEN 'very good' WHEN 3 THEN 'good'
         WHEN 4 THEN 'fair' WHEN 5 THEN 'poor'
         ELSE 'refused or unknown'
       END AS self_rated_health,
       COUNT(*) AS people
FROM questionnaire
GROUP BY self_rated_health
ORDER BY MIN(health);

This gives 2,303, 2,501, 2,847, 1,335 and 261 people from excellent to poor and 7 who refused or did not know. Two details of NULL handling apply to CASE. A WHEN condition that is NULL counts as not true, so CASE WHEN NULL THEN 'yes' ELSE 'no' END is 'no'. And because the second form compares with =, it cannot match NULL: CASE NULL WHEN NULL THEN 'equal' ELSE 'not equal' END is 'not equal'; testing for NULL needs the first form with IS NULL (SQLite, n.d.-a). In a real code table, a separate lookup table joined to the data is often better than a long CASE, since it keeps the labels in one place; the race table of this database is one.

COALESCE and NULLIF

COALESCE(a, b, ...) returns its first argument that is not NULL, and NULLIF(a, b) returns NULL when a = b and a otherwise. They convert between NULL and ordinary values in the two directions.

NULLIF is the tool for codes that stand for missing values. The examination file records a diastolic blood pressure of 0 for 394 of the 19,959 readings. The survey documentation allows such values, which arise in a reading by ear when the sounds can still be heard as the cuff pressure falls to zero, and the earlier articles on these data treat them as missing when averaging (Cleaning Data). NULLIF(dbp, 0) turns those zeros into NULL, so that averages and other aggregates skip them; Aggregate Functions shows by how much the mean changes. The same idea, with CASE because there are two codes, cleans the number of days a medicine has been taken: the column holds 282 entries coded 77777 (refused) or 99999 (don't know), and its largest raw value is 99999 days, about 274 years. Mapping the codes to NULL leaves a largest value of 21,900 days, 60 years.

COALESCE goes the other way, replacing NULL with a value, and it is easy to misuse. COALESCE(income_ratio, -1) turns the missing income ratio of person 93706 into -1, a number that any average or comparison will then treat as real. It is safe for display ("not recorded") and for genuine defaults, and unsafe as a way of making NULLs disappear before analysis, which is imputation by accident.

DISTINCT

SELECT DISTINCT removes duplicate rows from a result, turning SQL's multiset back into a set (Relational Databases). It applies to whole rows, so SELECT DISTINCT race, sex FROM person returns the 12 combinations that occur, where SELECT DISTINCT race returns 6.

For DISTINCT, NULLs count as equal to each other, unlike everywhere else: SELECT DISTINCT smq040 FROM smoking returns four rows, 1, 2, 3 and one NULL. The SQLite developers found this to be the behavior of every engine they tested and adopted it for compatibility, while noting that it sits oddly with NULLs being distinct in a UNIQUE column (SQLite, n.d.-b). Counting is different again: COUNT(DISTINCT smq040) is 3, because COUNT of a column ignores NULLs.

COUNT(DISTINCT ...) is often the right count when a table has more rows than the entity being counted: the prescription table has 14,434 rows but COUNT(DISTINCT seqn) is 4,045 people. A DISTINCT added to make duplicates disappear from a join is sometimes a sign that the join was wrong, a point taken up in Join Queries.

ORDER BY

Rows come back in no particular order unless ORDER BY asks for one. It takes a list of expressions, each sorted ascending (ASC, the default) or descending (DESC); the second expression breaks ties in the first, and so on. The expressions need not appear in the SELECT list, and they may refer to its aliases or to its columns by position (ORDER BY 2), though the position form breaks silently when the list is edited.

Where NULLs go

SQLite treats NULL as smaller than every other value, so NULLs come first in ascending order and last in descending order (SQLite, n.d.-c). Sorting people by income ratio, ascending, starts with the 1,231 people whose ratio is NULL. PostgreSQL and Oracle do the opposite: by default they sort NULLs as if larger than any other value, last in ascending order and first in descending order (PostgreSQL Global Development Group, n.d.-b; Oracle, n.d.), so the same query returns different first rows on different systems. The standard clauses NULLS FIRST and NULLS LAST settle it explicitly; SQLite supports them (SQLite, n.d.-c), and ORDER BY income_ratio NULLS LAST starts with the 91 people whose recorded ratio is 0.

Ties

When rows tie on every ORDER BY expression, their order is undefined (SQLite, n.d.-c). NHANES top-codes age at 80, and 427 people are recorded as 80. A query for "the five oldest people" is therefore not a well-defined question:

SELECT seqn, age FROM person ORDER BY age DESC LIMIT 5;

On the table as built, SQLite reads the rows in order of their key and returns persons 93768, 93785, 93800, 93816 and 93869. After an index on age is created, the same query is answered by reading the index backwards and returns persons 102890, 102883, 102871, 102856 and 102828. Both answers are correct; any five of the 427 are. A query whose result must be reproducible needs a tie-breaker that makes the order total, usually the key: ORDER BY age DESC, seqn.

LIMIT and OFFSET

LIMIT n keeps the first n rows of the sorted result, and OFFSET m skips m rows first, which is how results are shown a page at a time: LIMIT 5 OFFSET 5 gives rows 6 to 10. Without ORDER BY, "first" has no meaning, and a limited query returns whichever rows the plan meets first. With ties, even an ORDER BY may not be enough, as the age example shows.

The syntax is one of the least standardized parts of SQL:

Dialect First five rows
SQLite, PostgreSQL, MySQL ... ORDER BY x LIMIT 5
SQL Server SELECT TOP (5) ... ORDER BY x
SQL standard, PostgreSQL, Oracle, Db2 ... ORDER BY x FETCH FIRST 5 ROWS ONLY

The standard form has a variant that handles ties honestly, FETCH FIRST 5 ROWS WITH TIES, which returns the first five rows and every row tied with the fifth; PostgreSQL and Oracle support it (PostgreSQL Global Development Group, n.d.-c; Oracle, n.d.), and SQL Server has TOP (5) WITH TIES (Microsoft, n.d.). SQLite has no such clause, but a subquery gives the same result: find the value in fifth place, then keep every row at least that large.

SELECT COUNT(*) FROM person
WHERE age >= (SELECT age FROM person ORDER BY age DESC LIMIT 1 OFFSET 4);   -- 427

The answer to "the five oldest people, with ties" is 427 people, which is the honest answer to a question the top-coding makes unanswerable. Window functions, in Aggregate Functions, give a more general way to number and rank rows with ties.

OFFSET paging has a cost worth knowing: to return rows 10,001 to 10,010 the database must still produce and discard the first 10,000. For large results, paging by key (WHERE seqn > last_seen ORDER BY seqn LIMIT 10) avoids that.

The written order of clauses

With these clauses the full single-table query reads:

SELECT DISTINCT columns
FROM table
WHERE conditions
ORDER BY expressions
LIMIT n OFFSET m;

The clauses must be written in this order. They take effect in a different one: FROM, WHERE, then SELECT and DISTINCT, then ORDER BY, then LIMIT and OFFSET (SQLite, n.d.-c). That is why ORDER BY can sort by a column alias defined in SELECT, and why LIMIT applies after sorting, not before. Aggregate Functions adds GROUP BY and HAVING to the sequence.

In Python

import re
import sqlite3
import pandas as pd

con = sqlite3.connect("nhanes.db")

# a REGEXP operator for SQLite, implemented in Python
con.create_function("regexp", 2, lambda pattern, s: s is not None and re.search(pattern, s) is not None)
con.execute("SELECT name FROM drug WHERE name REGEXP '(^|; )[A-Z]+VASTATIN'").fetchall()

# the same filter in pandas, for comparison
drugs = pd.read_sql("SELECT * FROM drug", con)
drugs[drugs.name.str.endswith("STATIN") & (drugs.name != "NYSTATIN")]

SQLite turns X REGEXP Y into a call regexp(Y, X), so the function receives the pattern first (SQLite, n.d.-a). The numbers in this article are computed by tools/experiments/data-science/sql-operators-and-clauses.py in the site's repository.

References