SELECT and filtering

WHERE, execution order, sorting

How is a SELECT query structured?

The minimal query is SELECT and FROM. The other parts are optional, but are always written in this order:

SELECT   email, country          -- which columns
FROM     users                   -- from which table
WHERE    country = 'PL'          -- row filter
GROUP BY ...                     -- grouping
HAVING   ...                     -- group filter
ORDER BY created_at DESC         -- sorting
LIMIT    10;                     -- limiting the count

-- SELECT * returns all columns: convenient for exploration,
-- but in automated tests and reports it is better to list columns explicitly

In an interview: A query starts with SELECT and a list of columns, then FROM with a table, and then, as needed, WHERE for a filter, GROUP BY and HAVING for grouping, ORDER BY for sorting and LIMIT for restriction. For quick exploration I write SELECT star, but in automated tests I list the columns explicitly, so the test doesn't depend on changes to the table's structure.

In what order is a query actually executed?

The logical order differs from the order of writing:

FROM and JOIN: assemble rows from the tables
WHERE: filter the rows
GROUP BY: group
HAVING: filter the groups
SELECT: compute columns and aliases
DISTINCT: remove duplicates
ORDER BY: sort
LIMIT / OFFSET: cut off

Practical consequences: an alias from SELECT can't be used in WHERE, because at the WHERE moment it doesn't exist yet. But in ORDER BY it can, because sorting goes after SELECT. And an aggregate function can't be put in WHERE; for that there is HAVING.

-- error: the alias amount_usd doesn't exist yet at the WHERE stage
SELECT amount * 0.25 AS amount_usd FROM transactions WHERE amount_usd > 100;

-- correct: repeat the expression
SELECT amount * 0.25 AS amount_usd FROM transactions WHERE amount * 0.25 > 100;

-- and in ORDER BY the alias works
SELECT amount * 0.25 AS amount_usd FROM transactions ORDER BY amount_usd DESC;

In an interview: A query isn't executed in the order it is written: first FROM and JOIN, then WHERE, GROUP BY, HAVING, and only then SELECT, ORDER BY and LIMIT. That is why an alias from SELECT can't be used in WHERE, but can in ORDER BY, and why aggregates are filtered with HAVING and not with WHERE.

Which operators are used in WHERE?

Comparison: =, <> or !=, >, <, >=, <=.
Logical: AND, OR, NOT.
Sets and ranges: IN, BETWEEN, LIKE, IS NULL.

The trap is precedence: AND is evaluated before OR. Without parentheses a query may return something quite different.

-- intended: failed transactions of type deposit or withdrawal
-- in fact: (deposit) OR (withdrawal AND failed)
SELECT * FROM transactions
WHERE type = 'deposit' OR type = 'withdrawal' AND status = 'failed';

-- correct
SELECT * FROM transactions
WHERE (type = 'deposit' OR type = 'withdrawal') AND status = 'failed';

In an interview: In WHERE I use comparison operators, the logical AND, OR, NOT, as well as IN, BETWEEN, LIKE and IS NULL. The main trap is precedence: AND is evaluated before OR, so when combining them I always put parentheses, so that the query does exactly what I intended.

How do IN, BETWEEN and LIKE work?

IN: the value is in a list: status IN ('pending', 'failed').
BETWEEN: a range inclusive of both boundaries: amount BETWEEN 100 AND 500 is >= 100 AND <= 500.
LIKE: pattern search: % is any number of characters, _ is exactly one. In PostgreSQL ILIKE ignores case.

The BETWEEN trap with dates: created_at BETWEEN '2026-09-01' AND '2026-09-30' for a column with time will cut off the whole day of 30 September after 00:00. A half-open range is more reliable.

-- reliable for TIMESTAMP: including the 1st, without 1 October
SELECT * FROM transactions
WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01';

-- all gmail addresses, case-insensitively
SELECT email FROM users WHERE email ILIKE '%@gmail.com';

In an interview: IN checks membership in a list, BETWEEN a range inclusive of both boundaries, and LIKE searches by a pattern, where the percent sign is any number of characters and the underscore is one character. With dates I don't use BETWEEN: if the column has a time, the last day gets cut off. Instead I write "greater than or equal to the start and less than the next day".

How do you find rows where the value is NULL?

Only through IS NULL and IS NOT NULL. The condition = NULL gives UNKNOWN for every row, so the query will return zero rows with no error at all. This is especially treacherous: it seems that there is simply no data.

The second trap: WHERE status <> 'failed' won't return rows where status is NULL. If they are needed, the condition must be supplemented.

SELECT * FROM users WHERE phone IS NULL;       -- correct
SELECT * FROM users WHERE phone = NULL;        -- always 0 rows

-- everything except failed, including NULL
SELECT * FROM transactions
WHERE status <> 'failed' OR status IS NULL;

-- or in PostgreSQL
SELECT * FROM transactions WHERE status IS DISTINCT FROM 'failed';

In an interview: I look for empty values only through IS NULL, because a comparison "equals NULL" always gives "unknown" and returns zero rows without an error. And I remember the reverse trap: a "not equal" condition also skips rows with NULL, so if they are needed, I add OR IS NULL.

What does DISTINCT do?

DISTINCT removes duplicate rows from the result. Uniqueness is counted by the whole combination of the selected columns, not by the first one.

SELECT DISTINCT country FROM users;              -- a list of countries
SELECT DISTINCT currency, status FROM accounts;   -- unique pairs
SELECT COUNT(DISTINCT user_id) FROM accounts;    -- how many users have an account

In an interview: DISTINCT removes duplicates from the result, with uniqueness counted by the whole combination of columns. I use it to see the set of possible values, for example all the statuses in a table: it is a quick way to notice an unexpected value that isn't described in the documentation.

How do ORDER BY and LIMIT work?

ORDER BY col ASC is ascending (the default), DESC is descending. You can sort by several columns.
LIMIT n returns n rows, OFFSET k skips k. In SQL Server, instead of LIMIT there is TOP n or OFFSET … FETCH.

Two traps:

LIMIT without ORDER BY returns arbitrary rows. Without ORDER BY the order is not guaranteed anywhere: a test for "the first record" will be unstable.
NULL in sorting: in PostgreSQL NULL is considered greater than all values: at the end for ASC and at the start for DESC. In MySQL it is the other way round. Explicitly: NULLS LAST.

-- the 10 latest transactions; id as a second key makes the order stable
SELECT * FROM transactions
ORDER BY created_at DESC, id DESC
LIMIT 10;

In an interview: ORDER BY sorts in ascending or descending order, and LIMIT restricts the number of rows. Without ORDER BY the order isn't guaranteed, so LIMIT without sorting gives random rows and unstable tests. For stability I add a second sort key, for example id, and I remember that different DBMSs place NULL differently when sorting.