SELECT, WHERE & ORDER BY

A SQL query describes the result you want, not the steps to get it. The simplest shape is SELECT columns FROM table WHERE condition ORDER BY column LIMIT n. The database decides how to fetch the rows; you only state which rows and columns you want and how they should be sorted.

WHERE filters individual rows before anything is grouped or returned. Conditions combine with AND, OR and NOT and use operators such as =, <>, <, BETWEEN, IN, LIKE and IS NULL. ORDER BY sorts the final result, ascending by default, and LIMIT (or the standard FETCH FIRST n ROWS ONLY) cuts it to the first n rows.

NULL means 'unknown', not zero or empty. Any comparison with NULL, even NULL = NULL, gives UNKNOWN, and WHERE only keeps rows where the condition is TRUE. That one rule explains many surprising results.

1. 1The query is written as SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT, but it is logically evaluated in a different order: FROM, then WHERE, then GROUP BY and HAVING, then SELECT, then DISTINCT, then ORDER BY, then LIMIT/OFFSET.
2. 2FROM names the table (or joined tables) that supply the rows.
3. 3WHERE tests each row. A row survives only if the condition is TRUE; FALSE and UNKNOWN (anything involving NULL) are both thrown away.
4. 4SELECT computes the output columns, including expressions like salary * 12 and aliases like AS annual. Because this happens after WHERE, an alias cannot be used in WHERE, but it can be used in ORDER BY.
5. 5ORDER BY sorts the result by one or more columns, each ASC or DESC. Ties are broken by the next column listed; if you list none, tied rows can come back in any order.
6. 6LIMIT n keeps the first n rows of the sorted result; OFFSET k skips k rows first. LIMIT without ORDER BY returns an arbitrary n rows.

-- employees(id, name, dept, salary)
SELECT name,
       salary,
       salary * 12 AS annual          -- alias usable in ORDER BY, not in WHERE
FROM employees
WHERE dept IN ('Eng', 'Sales')
  AND salary > 80000                  -- NULL salaries are dropped here
ORDER BY salary DESC, id              -- id breaks ties deterministically
LIMIT 2;                              -- standard SQL: FETCH FIRST 2 ROWS ONLY

-- Rows with no salary: never use = NULL
SELECT name FROM employees WHERE salary IS NULL;

-- Case-insensitive prefix search
SELECT name FROM employees WHERE name ILIKE 'a%';   -- PostgreSQL
-- MySQL: WHERE name LIKE 'a%' (default collation is case-insensitive)

The first query returns Asha and Chen from the example table. salary * 12 AS annual is computed in SELECT, so it could be sorted on but not filtered on in WHERE. The second query is the only correct way to find missing salaries; WHERE salary = NULL returns zero rows.

Cost: Without a useful index, WHERE is a full scan, O(n). ORDER BY needs a sort, O(n log n), unless an index already provides the order. ORDER BY ... LIMIT k can use a top-k sort that only keeps k rows in memory.