A first query against the NHANES database of Relational Databases asks for the adults:
SELECT seqn, sex, age
FROM person
WHERE age >= 18
ORDER BY seqn
LIMIT 3;
| seqn | sex | age |
|---|---|---|
| 93705 | female | 66 |
| 93706 | male | 18 |
| 93708 | female | 66 |
The statement has the shape of almost every SQL query. FROM names the table to read. WHERE keeps the rows for which a condition is true. SELECT says which columns, or which values computed from them, to return. The last two lines, which sort the result and keep the first three rows, are covered in Special Operators and Clauses. Without them the query returns all 5,856 people aged 18 or over, in no promised order.
This article covers what can go in those three clauses: columns and expressions, operators and the order in which they bind, and conditions, including the behavior of NULL, which accounts for a large share of wrong answers from correct-looking SQL. The examples run in SQLite, the database built into Python, and the differences from PostgreSQL, MySQL and SQL Server are noted where they change a result.
SELECT and FROM
The SELECT list names the columns of the result, in the order wanted. SELECT * returns all columns of the table, which is convenient for a first look and fragile in code that is kept, because the result changes when a column is added to the table.
The list may contain expressions as well as columns. The examination table stores weight in kilograms, height in centimeters and the published body mass index, BMI, which is weight divided by the square of height in meters. The query can recompute it:
SELECT seqn, weight, height, bmi,
weight / ((height / 100) * (height / 100)) AS bmi_computed
FROM exam;
| seqn | weight | height | bmi | bmi_computed |
|---|---|---|---|---|
| 93703 | 13.7 | 88.6 | 17.5 | 17.452 |
| 93704 | 13.9 | 94.2 | 15.7 | 15.664 |
| 93705 | 79.5 | 158.3 | 31.7 | 31.725 |
The expression is evaluated once for each row, with the column names replaced by that row's values. AS gives the computed column a name; without it the result column is named after the expression text. Rounded to one decimal, the computed value agrees with the published bmi for all 8,005 people who have a weight, a height and a BMI, the same check that Enriching Data makes in pandas.
The result of a query is itself a table, with named columns and rows, but it is not stored anywhere. That it is a table is what allows queries to be nested inside other queries, the subject of Subqueries.
Operators
Arithmetic
SQL has the usual arithmetic operators +, -, *, / and % (remainder), and their behavior on integers is the first place where dialects part ways.
| Expression | SQLite result |
|---|---|
7 / 2 |
3 |
7 / 2.0 |
3.5 |
CAST(7 AS REAL) / 2 |
3.5 |
7 % 2 |
1 |
-7 / 2 |
-3 |
When both operands are integers, SQLite performs integer division, truncating toward zero (SQLite, n.d.-c). PostgreSQL and SQL Server do the same (PostgreSQL Global Development Group, n.d.-a; Microsoft, n.d.-a). MySQL does not: its / returns a decimal, 3.5000, and it has a separate operator, DIV, for integer division (MySQL, n.d.-a). The truncation is useful on purpose, age / 10 * 10 turns an age into its decade, and harmful by accident, when a proportion computed as a count divided by a count comes out as 0, an error shown in Aggregate Functions. Writing one operand as a real number, 2.0, or converting it with CAST, forces real division in every dialect.
Some notations differ more. String concatenation is || in standard SQL, SQLite, PostgreSQL and Oracle, while MySQL uses the function CONCAT: in MySQL's default mode || is a (deprecated) spelling of logical OR, and it concatenates only when the PIPES_AS_CONCAT mode is switched on (MySQL, n.d.-b). There is no standard exponentiation operator: ^ raises to a power in PostgreSQL (PostgreSQL Global Development Group, n.d.-a), means bitwise exclusive or in MySQL and SQL Server (MySQL, n.d.-c; Microsoft, n.d.-b), and is a syntax error in SQLite ("unrecognized token"). The function POWER(x, y) is the more portable spelling, and in SQLite is available when its math functions are compiled in, as they are in the Python used for these articles.
Comparison and logic
The comparison operators are =, <> (also written !=), <, <=, > and >=. Equality is a single =, not Python's ==, although SQLite accepts both. Comparisons work on text as well as numbers, but text compares character by character, so '10' < '9' is true: the character 1 sorts before 9. A number stored as text sorts like text, which is one reason column types matter.
The logical operators are AND, OR and NOT. They combine conditions, and the order in which they bind decides what a condition means. In SQLite, as in standard SQL, the order from tightest to loosest is: unary minus, then *, / and %, then + and -, then the comparisons, then NOT, then AND, then OR (SQLite, n.d.-c). Arithmetic binds before comparison, so weight / 2 > 40 compares half the weight with 40, as intended. The trap is AND and OR. Suppose the question is how many people are women or girls who are either 60 and over or under 18:
SELECT COUNT(*) FROM person
WHERE age >= 60 OR age < 18 AND sex = 'female'; -- 3,831
SELECT COUNT(*) FROM person
WHERE (age >= 60 OR age < 18) AND sex = 'female'; -- 2,742
The first query binds AND first and means "60 and over, or a girl under 18": the 2,150 people aged 60 and over of both sexes plus the 1,681 girls, 3,831 in all. Only the parenthesized version answers the question. Both run without error, and the wrong one returns a plausible number. Parentheses cost nothing and remove the question of precedence entirely; it is good practice to use them whenever AND and OR are mixed.
WHERE and three-valued logic
WHERE keeps a row when its condition is true. The SQLite documentation states the rule precisely: "Rows are excluded from the result if the WHERE clause evaluates to either false or NULL" (SQLite, n.d.-e). The word NULL in that sentence is the key to this section. A condition in SQL can have three values, not two.
Comparisons with NULL
NULL marks a missing value (Relational Databases describes the kinds of missing it stands for). Any comparison with it returns NULL, read as "unknown", because nothing is known about the missing value. That includes comparing NULL with NULL:
| Expression | Result |
|---|---|
NULL = NULL |
NULL |
NULL <> NULL |
NULL |
NULL IS NULL |
1 (true) |
1 + NULL |
NULL |
NULL * 0 |
NULL |
Arithmetic with NULL is also NULL, even multiplication by zero. When the SQLite developers set out to match other engines' handling of NULL, they tested PostgreSQL, Oracle, DB2, SQL Server, MySQL and others, and on these cases, and on the logic below, found them in agreement apart from one old version of MySQL (SQLite, n.d.-d). The consequence for queries is immediate. The ratio of family income to the poverty line is missing for 1,231 people, but a query that looks for them with = finds none:
SELECT COUNT(*) FROM person WHERE income_ratio = NULL; -- 0
SELECT COUNT(*) FROM person WHERE income_ratio IS NULL; -- 1,231
income_ratio = NULL is unknown for every row, so no row is kept. Testing for a missing value needs the operators IS NULL and IS NOT NULL, which always return true or false.
The truth tables
When conditions are combined, AND, OR and NOT extend to the third value by a consistent rule: if the result would be the same whatever the unknown value turned out to be, the result is that value; otherwise it is unknown. TRUE OR unknown is true, because an OR with one true side is true either way. FALSE AND unknown is false. TRUE AND unknown is unknown, because the answer depends on the missing value. SQLite computes the full tables (1 is true, 0 false):
| p | q | p AND q | p OR q | NOT p |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 0 |
| 1 | 0 | 0 | 1 | 0 |
| 1 | NULL | NULL | 1 | 0 |
| 0 | 1 | 0 | 1 | 1 |
| 0 | 0 | 0 | 0 | 1 |
| 0 | NULL | 0 | NULL | 1 |
| NULL | 1 | NULL | 1 | NULL |
| NULL | 0 | 0 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL |
SQLite's documentation notes that its operators handle NULL "in accordance with the SQL92 standard" (SQLite, n.d.-c).
A condition and its negation do not cover everything
In two-valued logic, every row satisfies either a condition or its negation. In SQL, rows where the condition is unknown satisfy neither:
SELECT COUNT(*) FROM person WHERE income_ratio < 1; -- 1,837
SELECT COUNT(*) FROM person WHERE NOT (income_ratio < 1); -- 6,186
SELECT COUNT(*) FROM person
WHERE income_ratio < 1 OR NOT (income_ratio < 1); -- 8,023
1,837 people live below the poverty line and 6,186 at or above it, 8,023 together; the remaining 1,231 of the 9,254 are in neither group, and even the condition "below or not below" excludes them. The same thing happens with <>. A query for everyone whose income ratio is not at the top code of 5 returns 6,815 rows and silently drops the 1,231 people whose ratio is unknown. Whether they belong in the answer is a question about the analysis, not about SQL, but it has to be asked. Writing income_ratio <> 5 OR income_ratio IS NULL, or in SQLite the shorter income_ratio IS NOT 5, includes them (8,046 rows). The standard spelling of the last form is IS DISTINCT FROM, which PostgreSQL supports and SQLite accepts from version 3.39.0 (SQLite, n.d.-f); the SQLite 3.38.4 in the Python used for this site's tests rejects it with a syntax error. SQLite's compact IS and IS NOT between two values are an extension (SQLite, n.d.-c).
The practical rule is to ask, for every condition on a column that can be NULL, which side the NULL rows should fall on, and to say so in the query. Special Operators and Clauses shows the same trap with NOT IN, where it can make a query return nothing at all.
Literals, quotes and types
Text literals take single quotes: sex = 'male'. In standard SQL, double quotes are for identifiers, names of tables and columns that would otherwise be illegal, such as a column called "All Cause". SQLite accepts double-quoted strings too, for compatibility with early MySQL, and this has a consequence its developers regret. If a double-quoted name does not match any column, SQLite treats it as a string. A misspelled column in double quotes is therefore not an error:
SELECT COUNT(*) FROM person WHERE "sexx" = 'male'; -- 0, no error
SELECT COUNT(*) FROM person WHERE sexx = 'male'; -- Error: no such column: sexx
The first query compares the string 'sexx' with 'male' for every row and returns 0. The SQLite documentation calls the feature a "misfeature" that lures newcomers "into the bad habit of using double-quoted string literals" (SQLite, n.d.-a). PostgreSQL would reject both queries with an error about the unknown column. Single quotes for strings and double quotes only for identifiers is the portable habit.
Comparisons between numbers and text follow each product's type rules. In SQLite, 1 = '1' is false, because an integer is not a string; the documentation notes that "every other major SQL database engine says this is true" (SQLite, n.d.-a). But when one side is a column with a numeric type, SQLite first converts a text operand that looks like a number, so age = '80' and age = 80 both find the 427 people recorded as 80 (SQLite, n.d.-b). The safe practice is not to rely on either behavior and to compare numbers with numbers.
The order in which clauses take effect
A query is written SELECT ... FROM ... WHERE ..., but its parts take effect in a different order. Logically, FROM comes first and produces the input rows; WHERE filters them; then SELECT computes the output columns for each surviving row; then ORDER BY sorts and LIMIT cuts. The SQLite documentation describes this sequence, with the caution that it "is purely illustrative" and that no engine "is required to follow this or any other specific process" (SQLite, n.d.-e); what is fixed is the result, not the procedure. Aggregate Functions adds grouping to the sequence and draws it in full.
The order explains a rule of standard SQL: a column alias defined in SELECT cannot be used in WHERE, because WHERE takes effect before the alias exists. PostgreSQL rejects the following query, its documentation stating that an output column's name can be used in ORDER BY and GROUP BY "but not in the WHERE or HAVING clauses" (PostgreSQL Global Development Group, n.d.-b); SQLite, as an extension, accepts it and returns the four people whose computed BMI exceeds 70:
SELECT seqn, weight / ((height / 100) * (height / 100)) AS bmi_computed
FROM exam
WHERE bmi_computed > 70;
The portable form repeats the expression in the WHERE clause, or computes it in a subquery or common table expression and filters outside (Subqueries). An alias can be used in ORDER BY in every dialect, because sorting comes after SELECT.
Declarative queries and query plans
A query describes a result, not a method. The database's query planner chooses the method, and SQLite will report its choice when a query is prefixed with EXPLAIN QUERY PLAN. For the query
SELECT seqn, bmi FROM exam WHERE bmi > 70;
the plan on the table as built is SCAN exam: read every one of the 8,704 rows and test each. After CREATE INDEX exam_bmi ON exam (bmi), the plan for the unchanged query becomes SEARCH exam USING COVERING INDEX exam_bmi (bmi>?): jump to the first entry above 70 in the sorted index and read from there. Both plans return the same four people (in different orders, as Relational Databases showed). On a table of millions of rows the difference in time can be large, and it is obtained without changing the query. This is the practical meaning of Codd's data independence, and the reason a data scientist can usually write the query that states the question and leave the plan to the database. When a query is slow, EXPLAIN QUERY PLAN (EXPLAIN in PostgreSQL and MySQL) is the first place to look.
Comments and layout
Two hyphens start a comment that runs to the end of the line, and /* ... */ encloses a comment of any length. Commenting out a line is a common way to try a query with and without a condition:
SELECT seqn, age -- the person and age
FROM person
-- WHERE age >= 18
ORDER BY age DESC, seqn;
Keywords are not case-sensitive and line breaks are ignored, so layout is for readers. The convention used in these articles, keywords in capitals and each clause on its own line, makes the structure of a query visible at a glance. A semicolon ends a statement; Python's sqlite3 runs one statement per call and does not need it.
In Python
import sqlite3
con = sqlite3.connect("nhanes.db")
rows = con.execute("""
SELECT seqn, sex, age
FROM person
WHERE age >= ? AND income_ratio IS NOT NULL
""", (18,)).fetchall()
con.execute("EXPLAIN QUERY PLAN SELECT seqn FROM exam WHERE bmi > 70").fetchall()
The ? is a placeholder for a value supplied separately, the safe way to put Python values into a query, explained with the rest of the sqlite3 interface in Queries in Python. The numbers in this article are computed by tools/experiments/data-science/simple-sql-queries.py in the site's repository, which builds the database from the public NHANES files with sqldata.py.
References
- Microsoft (n.d.-a). / (Division) (Transact-SQL). https://learn.microsoft.com/en-us/sql/t-sql/language-elements/divide-transact-sql record
- Microsoft (n.d.-b). ^ (Bitwise exclusive OR) (Transact-SQL). https://learn.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-exclusive-or-transact-sql record
- MySQL (n.d.-a). Arithmetic operators. MySQL 8.4 Reference Manual. https://dev.mysql.com/doc/refman/8.4/en/arithmetic-functions.html record
- MySQL (n.d.-b). Logical operators. MySQL 8.4 Reference Manual. https://dev.mysql.com/doc/refman/8.4/en/logical-operators.html record
- MySQL (n.d.-c). Bit functions and operators. MySQL 8.4 Reference Manual. https://dev.mysql.com/doc/refman/8.4/en/bit-functions.html record
- PostgreSQL Global Development Group (n.d.-a). Mathematical functions and operators. PostgreSQL documentation. https://www.postgresql.org/docs/current/functions-math.html record
- PostgreSQL Global Development Group (n.d.-b). SELECT. PostgreSQL documentation. https://www.postgresql.org/docs/current/sql-select.html record · 4 pages
- SQLite (n.d.-a). Quirks, caveats, and gotchas in SQLite. https://sqlite.org/quirks.html record · 3 pages
- SQLite (n.d.-b). Datatypes in SQLite. https://sqlite.org/datatype3.html record
- SQLite (n.d.-c). SQL language expressions. https://sqlite.org/lang_expr.html record · 4 pages
- SQLite (n.d.-d). NULL handling in SQLite versus other database engines. https://sqlite.org/nulls.html record · 2 pages
- SQLite (n.d.-e). The SELECT statement. https://sqlite.org/lang_select.html record · 5 pages
- SQLite (n.d.-f). Release 3.39.0 on 2022-06-25. https://sqlite.org/releaselog/3_39_0.html record · 3 pages