SELECT, WHERE, and the Order of Evaluation

You write a query that reads well top to bottom: select the amount, filter on the amount, sort by the amount. The engine answers with Unknown column 'amount' in 'where clause' and you rename things until it stops complaining. The clause that fails can only see what earlier clauses have computed, and the SELECT list is not one of them.

The same fact explains a bug that produces no error at all. You run a report with LIMIT 20, nobody mentions ORDER BY, and a week later the numbers have changed although no row was updated. The rows were always arbitrary; you got a different arbitrary set.

The examples below run on MySQL 9.6.0. The dataset starts with five customers, seven orders and a call log, and it stays the same for the rest of the module. SQLite 3.51.0 was run alongside every query that both engines accept; differences are called out where they exist.

FROM runs first and LIMIT runs last

The evaluation order is fixed and it is not the order you type: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. The engine has to know which tables it is talking about before it can resolve a column name anywhere else.

CREATE TABLE customer (
  customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email       VARCHAR(160) NOT NULL,
  city        VARCHAR(64)  NULL,
  UNIQUE KEY uq_customer_email (email)
) ENGINE=InnoDB;

CREATE TABLE orders (
  order_id    INT NOT NULL PRIMARY KEY,
  customer_id INT NOT NULL,
  status      VARCHAR(16) NOT NULL,
  placed_at   DATETIME NOT NULL,
  total_cents INT NOT NULL,
  KEY idx_orders_customer (customer_id),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
    REFERENCES customer (customer_id)
) ENGINE=InnoDB;

CREATE TABLE call_log (
  call_id     INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NULL,
  note        VARCHAR(64) NOT NULL
) ENGINE=InnoDB;

INSERT INTO customer (customer_id, email, city) VALUES
 (1, 'ana@example.com',  'Lisbon'),
 (2, 'bo@example.com',   'Porto'),
 (3, 'cai@example.com',  NULL),
 (4, 'dee@example.com',  'Braga'),
 (5, 'eve@example.com',  'Lisbon');

INSERT INTO orders (order_id, customer_id, status, placed_at, total_cents) VALUES
 (1001, 1, 'paid',     '2026-03-02 09:14:00', 12000),
 (1002, 1, 'paid',     '2026-03-11 16:40:00',  4500),
 (1003, 2, 'pending',  '2026-03-12 11:02:00',  9900),
 (1004, 4, 'refunded', '2026-04-01 08:20:00',  3000),
 (1005, 2, 'paid',     '2026-04-04 19:55:00', 22500),
 (1006, 4, 'pending',  '2026-04-09 10:10:00',  1800),
 (1007, 1, 'paid',     '2026-04-15 13:05:00',  4500);

FROM assembles the rows. WHERE throws rows away. GROUP BY collapses them into groups. HAVING throws groups away. SELECT computes the result columns. ORDER BY sorts. LIMIT truncates. Only one of those knows about the word amount in your SELECT list, and it is not WHERE.

Aliases and aggregates are both findings of later clauses, which is why the two questions everyone has about SQL are one question. WHERE runs before the projection exists, so it cannot filter on it. LIMIT runs after the sort, so with no sort there is nothing to cut from except an unordered bag of rows.

A SELECT alias does not exist yet when WHERE runs

The failing query is the natural one to write:

SELECT order_id, total_cents / 100 AS amount
FROM orders
WHERE amount > 100;

ERROR 1054 (42S22): Unknown column 'amount' in 'where clause'

The message is accurate. amount is introduced by the SELECT list, and WHERE is evaluated two steps earlier, over rows that have no projection. Repeat the expression, or filter on the source columns:

SELECT order_id, total_cents / 100 AS amount
FROM orders
WHERE total_cents > 10000
ORDER BY amount DESC;

+----------+----------+
| order_id | amount   |
+----------+----------+
|     1005 | 225.0000 |
|     1001 | 120.0000 |
+----------+----------+

That works, and the reason is worth noticing. ORDER BY runs after SELECT, so it can see amount even though WHERE cannot. MySQL and SQLite both accept the alias there. HAVING runs after SELECT as well, which is why the next section works without repeating the expression.

HAVING exists because WHERE cannot see an aggregate

An aggregate has no value until the rows have been grouped, and grouping happens after WHERE, so WHERE COUNT(*) > 2 is not inconvenient but impossible. HAVING runs after the grouping; that is its entire job.

SELECT c.customer_id, c.email, COUNT(*) AS paid_orders,
       SUM(o.total_cents) / 100.0 AS paid_euros
FROM customer c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status <> 'pending'
GROUP BY c.customer_id, c.email
HAVING COUNT(*) >= 2 AND SUM(o.total_cents) > 15000
ORDER BY paid_euros DESC;

+-------------+-----------------+-------------+------------+
| customer_id | email           | paid_orders | paid_euros |
+-------------+-----------------+-------------+------------+
|           1 | ana@example.com |           3 |   210.0000 |
+-------------+-----------------+-------------+------------+

Customer 1 has three paid orders and 210 euros, and passes both conditions. Customer 2 has one paid order, because 1003 is pending and WHERE removed it before the grouping, so the count condition is what cuts it. Both conditions live in HAVING for the same reason: WHERE cannot see an aggregate, and the grouping has not happened yet when it runs.

LIMIT without ORDER BY is not reproducible

Nothing about a table guarantees an order. Rows come back in whatever order the access path produces, and the optimizer changes its mind when an index appears. In SQLite, adding one index changes the same query's output. The DDL below is SQLite's; in MySQL the date column would be DATETIME, and the rest is the same:

CREATE TABLE ticket (
  id        INTEGER PRIMARY KEY,
  opened_at TEXT NOT NULL,
  subject   TEXT NOT NULL
);

INSERT INTO ticket VALUES
 (1, '2026-01-05', 'locked out'),
 (2, '2026-01-02', 'invoice wrong'),
 (3, '2026-01-09', 'slow admin'),
 (4, '2026-01-03', 'refund'),
 (5, '2026-01-07', 'cannot log in');

SELECT id FROM ticket LIMIT 3;

┌────┐
│ id │
├────┤
│ 1  │
│ 2  │
│ 3  │
└────┘

Now create an index on opened_at, run ANALYZE, and run the identical statement again:

CREATE INDEX idx_ticket_opened ON ticket (opened_at);
ANALYZE;

SELECT id FROM ticket LIMIT 3;

┌────┐
│ id │
├────┤
│ 2  │
│ 4  │
│ 1  │
└────┘

The query text did not change and the data did not change. The plan changed from SCAN ticket to SCAN ticket USING COVERING INDEX idx_ticket_opened, and the first three rows became a different three rows. In MySQL I could not produce the flip on a five-row table, because InnoDB chose a primary key scan both times; the instability is there, it needs a larger table or a different index. If a query is supposed to return the twenty most recent tickets, the ORDER BY is part of the question.

LIMIT also cannot be pushed before ORDER BY, or the sort would be sorting an arbitrary subset. That is why ORDER BY is not optional whenever LIMIT is present. The ANSI spelling is FETCH FIRST 20 ROWS ONLY; MySQL uses LIMIT, and SQLite accepts both.

NULL is unknown, not false

SQL comparisons are three-valued: true, false, unknown. A comparison with NULL returns unknown, and WHERE keeps a row only when the predicate is true. Unknown is discarded along with false, which is why this query is empty rather than an error:

SELECT customer_id, email, city FROM customer WHERE city = NULL;

Empty set (0.00 sec)

Use IS NULL, or a null-safe comparison when either side may be NULL. MySQL spells that <=>; SQLite and PostgreSQL spell it IS DISTINCT FROM. MySQL 9.6 has no IS DISTINCT FROM:

SELECT customer_id, city FROM customer WHERE city IS NULL;

SELECT customer_id, city FROM customer WHERE city <=> NULL;

+-------------+------+
| customer_id | city |
+-------------+------+
|           3 | NULL |
+-------------+------+

The same three-valued logic explains a trap that eats more production queries than any other. NOT IN with a NULL anywhere in the list returns no rows, because x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and the last term is unknown for every value of x. Unknown propagates through AND.

INSERT INTO call_log (call_id, customer_id, note) VALUES
 (1, 1,    'asked for invoice'),
 (2, NULL, 'wrong number'),
 (3, 3,    'asked for invoice'),
 (4, 99,   'orphan import');

SELECT COUNT(*) AS customers_without_calls
FROM customer
WHERE customer_id NOT IN (SELECT customer_id FROM call_log);

+--------------------------+
| customers_without_calls  |
+--------------------------+
|                        0 |
+--------------------------+

The call log contains a NULL, the subquery includes it, and every customer is eliminated. NOT EXISTS asks a different question, one with only two possible answers, and returns the rows you wanted:

SELECT COUNT(*) AS customers_without_calls
FROM customer c
WHERE NOT EXISTS (
  SELECT 1 FROM call_log l WHERE l.customer_id = c.customer_id
);

+--------------------------+
| customers_without_calls  |
+--------------------------+
|                        3 |
+--------------------------+

Three customers: bo, dee and eve. The orphan call with customer_id = 99 is invisible to this query, which is correct for the question being asked. The NULL in the list is what makes the two statements disagree, not the data being wrong.

LIKE matches a pattern, and a leading wildcard costs a scan

LIKE compares against a pattern where % stands for any sequence of characters and _ stands for exactly one. Backslash is the default escape character, so a literal underscore needs escaping:

SELECT 'axb' LIKE 'a_b'    AS one_char,
       'axxb' LIKE 'a_b'   AS two_chars,
       'a_b' LIKE 'a\_b'   AS literal_underscore;

+----------+-----------+--------------------+
| one_char | two_chars | literal_underscore |
+----------+-----------+--------------------+
|        1 |         0 |                  1 |
+----------+-----------+--------------------+

Case sensitivity belongs to the engine and the collation, not to LIKE. MySQL's connection collation here is utf8mb4_0900_ai_ci, where ai is accent-insensitive and ci case-insensitive, so email LIKE 'ana%' matches ana@example.com unaided. PostgreSQL's LIKE is case-sensitive and ILIKE the variant. SQLite's LIKE is case-insensitive for ASCII, GLOB is case-sensitive, and SQLite 3.51 has no ILIKE, so a PostgreSQL query will not parse there.

The part that matters for performance is where the wildcard sits. Only a pattern with a literal prefix can be turned into an index range. On a separate 50,000-row subscription table with an index on email, MySQL reports the difference in the estimated rows directly:

CREATE TABLE subscription (
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(160) NOT NULL,
  KEY idx_sub_email (email)
) ENGINE=InnoDB;

EXPLAIN SELECT id FROM subscription WHERE email LIKE 'user000042%';
EXPLAIN SELECT id FROM subscription WHERE email LIKE '%user000042%';

prefix:           type=range  key=idx_sub_email  rows=1
leading wildcard: type=index  key=idx_sub_email  rows=48000

MySQL's default EXPLAIN output is wide enough to wrap in a terminal, so those are the two columns that matter, extracted from it. range with rows=1 means the index narrowed the search to a single entry. index with a row estimate in the tens of thousands means the engine read the whole index and applied the pattern to every entry: a scan wearing an index as a costume. The estimate moves between runs and after ANALYZE, which is expected for a number the optimizer guesses at. Counting the matches shows the same thing at this size, at about 13 milliseconds for the prefix and about 24 for the pattern that starts with %, against 18 for a plain COUNT(*) of the table, measured through the MySQL client on one machine.

SQLite is stricter and clearer about this. Its query planner will not use an index for LIKE unless the column has a case-insensitive collation, because plain LIKE is case-insensitive and an ordinary index is not:

CREATE INDEX idx_sub_email ON subscription (email);
EXPLAIN QUERY PLAN SELECT id FROM subscription WHERE email LIKE 'user000042%';

CREATE INDEX idx_sub_email_nc ON subscription (email COLLATE NOCASE);
EXPLAIN QUERY PLAN SELECT id FROM subscription WHERE email LIKE 'user000042%' COLLATE NOCASE;

`--SCAN subscription
`--SEARCH subscription USING COVERING INDEX idx_sub_email_nc (email>? AND email<?)

Two plans for the same query shape, and two fixes: keep the wildcard suffix-only, or add a case-insensitive or functional index. Past a prefix, the tools are a full-text index or a search service, and LIKE '%...%' on a large table is where that conversation starts.

Write the clause order into the query

The three clauses people get wrong are all explained by the same ordering. Filter rows in WHERE, filter groups in HAVING, and never assume a projection exists before SELECT has run. When a filter needs an alias, wrap the projection in a derived table and filter outside it, which is what lesson three does with window functions.

Two habits are worth keeping. Write ORDER BY in the same edit as any LIMIT. And when a comparison has to handle missing data, decide explicitly whether NULL is a value you want, then write IS NULL or IS NOT NULL rather than leaving the answer to three-valued logic.

SELECT order_id, placed_at, total_cents / 100 AS amount
FROM orders
ORDER BY placed_at DESC, order_id DESC
LIMIT 20;

SELECT customer_id, email FROM customer WHERE city IS NULL;

SELECT c.customer_id, c.email
FROM customer c
WHERE NOT EXISTS (
  SELECT 1 FROM call_log l WHERE l.customer_id = c.customer_id
);

+-------------+-----------------+
| customer_id | email           |
+-------------+-----------------+
|           2 | bo@example.com  |
|           4 | dee@example.com |
|           5 | eve@example.com |
+-------------+-----------------+

SELECT customer_id, email FROM customer WHERE email LIKE '%example.com%';