HowToCodePage

SELECT: reading data

Choosing columns, filtering rows, sorting, and limiting — the shape of nearly every query you will write.

The shape of a query

SQL clauses are written in English order but evaluated differently. Understanding that order explains why aliases work in some clauses and not others.

SELECT   name, price            -- 5. choose columns
FROM     products               -- 1. source table
WHERE    price > 20             -- 2. filter rows
GROUP BY category               -- 3. collapse groups
HAVING   COUNT(*) > 1           -- 4. filter groups
ORDER BY price DESC             -- 6. sort
LIMIT    10;                    -- 7. restrict rows
⚠️
SELECT * is fine for exploration, terrible for production: it breaks callers when columns change and transfers data you never use.

Columns and aliases

SELECT DISTINCT category FROM products;

SELECT name, price * 1.2 AS price_with_tax
FROM products;

-- CASE for conditional labels
SELECT name,
  CASE WHEN price >= 100 THEN 'premium'
       WHEN price >= 20  THEN 'mid'
       ELSE 'budget' END AS tier
FROM products;

Because of evaluation order, an alias defined in SELECT cannot be used in WHERE — the filter runs first. Repeat the expression or wrap it in a subquery/CTE.

Filtering rows

OperatorUse
=, <>Equal / not equal (some dialects accept !=)
BETWEEN 10 AND 20Inclusive range
IN ('a','b')Any of a list
LIKE 'app%'Pattern; % many, _ one
IS NULLNull test — never = NULL
AND/OR/NOTCombine; AND binds tighter than OR
SELECT * FROM users
WHERE country = 'US'
  AND (plan = 'pro' OR trial_ends > CURRENT_DATE)
  AND email IS NOT NULL;
⚠️
NULL comparisons yield UNKNOWN, not false — so WHERE x <> 5 silently excludes rows where x IS NULL. Add an explicit IS NULL branch when that matters.

Sorting and paging

SELECT name, price
FROM products
ORDER BY category ASC, price DESC
LIMIT 20 OFFSET 40;   -- page 3
  • Offset paging gets slower as you go deeper — the database still walks skipped rows.
  • For large tables use keyset pagination: WHERE id > :last_id ORDER BY id LIMIT 20.
  • Always include a deterministic tiebreaker (like id) or page boundaries can repeat and skip rows.

FAQ

Why is my ORDER BY slow?
It cannot use an index, usually because the sort columns do not match an index prefix or you mixed ASC/DESC. An index on (category, price) serves ORDER BY category, price directly.
Does column order in SELECT matter?
Only for readability and for positional client code — never assume order without naming columns.

Joins Aggregation and GROUP BY

Last refreshed 2026-09-17.