SELECT, DISTINCT and WHERE

Article · 2:00

Asking for rows, and the operators that decide which ones come back - including the one that behaves unlike all the others.

SELECT names the columns, FROM names the table, WHERE filters. SELECT * is fine while exploring and a liability in application code: it fetches columns you do not need, breaks when a column is added, and prevents a covering index from being used.

DISTINCT removes duplicate rows from the result. It applies to the whole row, not to one column, which is the usual surprise - SELECT DISTINCT city, name gives distinct combinations.

The operators in WHERE are mostly what you expect. AND, OR and NOT combine conditions, and AND binds tighter than OR, so parentheses are not optional once you mix them. IN tests a list, BETWEEN tests an inclusive range, and LIKE matches a pattern where % is any run of characters and _ is exactly one.

Then NULL, which is the one that catches everybody. NULL means unknown, and no comparison with it is ever true - not = NULL, not != NULL, not even NULL = NULL. Testing it needs IS NULL or IS NOT NULL. It follows that WHERE status != 'paid' silently drops every row whose status is NULL, because unknown is not known to be different either.

-- Name the columns in application code. SELECT * breaks on a schema
-- change and stops a covering index being usable.
SELECT id, name, city FROM customers;

-- DISTINCT applies to the whole ROW, not to the first column.
SELECT DISTINCT city FROM customers;
SELECT DISTINCT city, name FROM customers;   -- distinct COMBINATIONS

-- AND binds tighter than OR, so the parentheses change the meaning.
SELECT id, name, total
FROM orders
WHERE (status = 'paid' OR status = 'shipped')
  AND total > 100;

SELECT * FROM orders WHERE status IN ('paid', 'shipped');
SELECT * FROM orders WHERE total BETWEEN 50 AND 100;      -- inclusive
SELECT * FROM customers WHERE email LIKE '%@example.com'; -- % = any run
SELECT * FROM customers WHERE sku  LIKE 'SKU-_______';    -- _ = exactly one

-- NULL is unknown, so nothing compares equal to it - not even NULL.
SELECT * FROM orders WHERE notes IS NULL;       -- correct
-- SELECT * FROM orders WHERE notes = NULL;     -- always zero rows

-- And the trap that follows: this silently excludes NULL statuses.
SELECT * FROM orders WHERE status != 'paid';
-- What you probably meant:
SELECT * FROM orders WHERE status != 'paid' OR status IS NULL;