SELECT, WHERE, ORDER BY

TL;DR

Every SQL query you’ll ever write is a variation on this skeleton:

SELECT  <columns>
FROM    <table>
WHERE   <row filter>
ORDER BY <expression>
LIMIT   <n>;

FROM says where the rows live. WHERE throws out rows you don’t want. SELECT picks which columns survive. ORDER BY sorts the result. LIMIT clips it. That’s 80% of SQL by line count, in five clauses.

The three things beginners get wrong, every time: (1) thinking WHERE runs after SELECT (it runs before — you can’t filter on a column alias defined in SELECT); (2) assuming rows come back in a particular order without ORDER BY (they don’t, and the order can change between runs); (3) writing SELECT * in production (it’s lazy and breaks when the schema evolves).

A worked example

The same example dataset will run through this guide and the next few. Build it once and you can paste every query straight into psql:

CREATE TABLE users (
    id           int PRIMARY KEY,
    name         text,
    email        text,
    country      text,
    signed_up_at timestamptz
);

INSERT INTO users VALUES
  (1, 'Alice',   'alice@x.com',  'US', '2026-01-15 10:00+00'),
  (2, 'Bob',     'bob@x.com',    'US', '2026-02-01 12:30+00'),
  (3, 'Carol',   'carol@x.com',  'UK', '2026-02-10 09:15+00'),
  (4, 'Dinesh',  'd@x.com',      'IN', '2026-03-05 06:00+00'),
  (5, 'Eve',     'eve@x.com',    'UK', '2026-03-22 18:45+00'),
  (6, 'Frank',   NULL,           'US', '2026-04-01 11:00+00'),
  (7, 'Gabi',    'gabi@x.com',   'DE', '2026-04-12 08:00+00');

SELECT — picking columns

SELECT is projection: from each row, take a subset (and possibly compute new) columns.

SELECT name, country FROM users;

+--------+---------+
| name   | country |
+--------+---------+
| Alice  | US      |
| Bob    | US      |
| Carol  | UK      |
| Dinesh | IN      |
| Eve    | UK      |
| Frank  | US      |
| Gabi   | DE      |
+--------+---------+

You can compute expressions, not just pick columns:

SELECT name,
       UPPER(country)                       AS country_upper,
       LENGTH(name)                         AS name_len,
       EXTRACT(YEAR FROM signed_up_at)      AS signup_year
FROM   users
LIMIT  3;

+--------+---------------+----------+-------------+
| name   | country_upper | name_len | signup_year |
+--------+---------------+----------+-------------+
| Alice  | US            |        5 |        2026 |
| Bob    | US            |        3 |        2026 |
| Carol  | UK            |        5 |        2026 |
+--------+---------------+----------+-------------+

AS names the output column. Without it, the column gets a generated name (upper, length, etc.) which is fine in ad-hoc queries and miserable in production code.

SELECT * — when it’s fine, when it isn’t

SELECT * is fine in psql and notebooks for exploration. It’s a bug magnet in checked-in code:

• New columns silently flow into your result set, possibly into your model features.
• On column-store warehouses (Snowflake, BigQuery), SELECT * reads every column from disk; on a wide table that’s 100x the cost of SELECT a, b.
• When you INSERT INTO target SELECT * FROM source, schema changes break the insert with no warning until production.

Rule: SELECT * for poking around, named columns for everything else.

DISTINCT — drop duplicate rows

SELECT DISTINCT country FROM users;
-- US, UK, IN, DE  (in some order)

DISTINCT deduplicates the entire row, not a single column. SELECT DISTINCT country, name returns one row per (country, name) pair, not one row per country. For “one row per group with extras”, you want GROUP BY or window functions, not DISTINCT.

WHERE — filtering rows

WHERE runs after FROM and before GROUP BY / SELECT. Each row in the table is tested against the predicate; rows where the predicate is TRUE survive.

SELECT name, country
FROM   users
WHERE  country = 'US';

+-------+---------+
| name  | country |
+-------+---------+
| Alice | US      |
| Bob   | US      |
| Frank | US      |
+-------+---------+

The operators worth memorizing

A composite filter:

SELECT name, country, signed_up_at
FROM   users
WHERE  country IN ('US', 'UK')
  AND  signed_up_at >= '2026-02-01'
  AND  email IS NOT NULL
ORDER  BY signed_up_at;

+-------+---------+------------------------+
| name  | country | signed_up_at           |
+-------+---------+------------------------+
| Bob   | US      | 2026-02-01 12:30:00+00 |
| Carol | UK      | 2026-02-10 09:15:00+00 |
| Eve   | UK      | 2026-03-22 18:45:00+00 |
+-------+---------+------------------------+

Note Frank is excluded — email IS NOT NULL killed his row. NULL-handling is a whole guide (SQL 109).

WHERE cannot reference SELECT aliases

-- ERROR: column "name_len" does not exist
SELECT name, LENGTH(name) AS name_len
FROM   users
WHERE  name_len > 4;

The reason is the execution order: WHERE runs before SELECT, so the alias hasn’t been created yet. Workarounds:

-- Option 1: repeat the expression
SELECT name, LENGTH(name) AS name_len
FROM   users
WHERE  LENGTH(name) > 4;

-- Option 2: wrap in a subquery / CTE
WITH t AS (
    SELECT name, LENGTH(name) AS name_len FROM users
)
SELECT * FROM t WHERE name_len > 4;

ORDER BY can reference aliases — that’s a quirk, not a contradiction: ordering happens after SELECT.

ORDER BY — sorting the result

SELECT name, signed_up_at
FROM   users
ORDER  BY signed_up_at DESC
LIMIT  3;

+-------+------------------------+
| name  | signed_up_at           |
+-------+------------------------+
| Gabi  | 2026-04-12 08:00:00+00 |
| Frank | 2026-04-01 11:00:00+00 |
| Eve   | 2026-03-22 18:45:00+00 |
+-------+------------------------+

ASC is the default; DESC is descending. You can sort by:

• A column name: ORDER BY signed_up_at.
• A SELECT alias: ORDER BY signup_year DESC.
• A column position: ORDER BY 2 DESC (sorts by the 2nd select column). Concise in psql; brittle in checked-in code — when somebody adds a column the position shifts.
• An expression: ORDER BY LOWER(name) for case-insensitive sort.
• Multiple keys: ORDER BY country ASC, signed_up_at DESC — primary sort by country ascending, then within each country by signup descending.

NULLs in ORDER BY

PostgreSQL puts NULLs last for ASC and first for DESC by default. Override with NULLS FIRST or NULLS LAST:

SELECT name, email FROM users ORDER BY email NULLS LAST;

This is a dialect quibble — MySQL puts NULLs first for ASC. If you care about portability, always specify NULLS FIRST/LAST explicitly.

Without ORDER BY, order is undefined

This bites every beginner exactly once:

SELECT name FROM users LIMIT 3;
-- Returns SOME 3 rows. Possibly the first 3 in insertion order.
-- Possibly not. May change after a VACUUM or schema change.

Postgres, Snowflake, BigQuery, ClickHouse — none of them promise an order without ORDER BY. The “stable” order you see during development is an accident of how the rows happen to live on disk today. Production code that depends on it will silently break.

LIMIT and OFFSET — pagination

SELECT name FROM users ORDER BY id LIMIT 3;             -- first page
SELECT name FROM users ORDER BY id LIMIT 3 OFFSET 3;    -- second page

LIMIT and OFFSET together do classic pagination, but OFFSET is a performance trap: the engine still has to read and discard the skipped rows. OFFSET 1000000 reads a million rows and throws them away. For deep pagination, use keyset pagination — order by an indexed column and filter on the last seen value:

-- "give me the next page after id=42"
SELECT * FROM users WHERE id > 42 ORDER BY id LIMIT 20;

This is O(log n) seek + O(20) read regardless of how deep you’ve paginated.

Dialect note. SQL Server and older Oracle use TOP n and FETCH FIRST n ROWS instead of LIMIT. Postgres, MySQL, SQLite, Snowflake, BigQuery, DuckDB, ClickHouse all accept LIMIT.

Common pitfalls

• WHERE x = NULL returns nothing. Use IS NULL. NULL comparisons yield UNKNOWN, which WHERE treats as FALSE.
• Forgetting parens around mixed AND/OR. WHERE a AND b OR c parses as (a AND b) OR c. Almost never what you meant. Always parenthesise.
• LIKE without indexes is a sequential scan. A %foo% pattern can’t use a B-tree at all (the prefix is unknown). For substring search at scale you want pg_trgm or a real search engine.
• ORDER BY with LIMIT is not “top-N then sort” — it’s “sort then top-N”. Postgres can use a heap for top-N if the sort key is selective; otherwise it sorts the entire result. Watch EXPLAIN.
• String comparisons are collation-sensitive. 'a' < 'B' depends on the column’s collation. In Postgres, default is locale-dependent; COLLATE "C" gives byte-order comparison.

Production patterns for ML feature pipelines

The simplest feature pipelines are often just SELECT … WHERE … GROUP BY written carefully. Two patterns to internalize early:

1. Always filter on indexed columns first. When building a training slice, the time-range predicate carries most of the selectivity:

SELECT user_id, country, signed_up_at
FROM   users
WHERE  signed_up_at >= '2026-04-01'
  AND  signed_up_at <  '2026-05-01';

If signed_up_at has a B-tree index (it usually does), the planner does an index range scan and reads only the April rows from disk. Without it, full table scan.

2. Lock down SELECT columns in checked-in pipelines. The training data your model sees should change only when you intend it to. Naming columns explicitly is how you keep that contract:

SELECT user_id, country, signed_up_at, total_orders, lifetime_value
FROM   feature_users
WHERE  signed_up_at >= :as_of_date - INTERVAL '90 days';

When somebody adds social_security_number to feature_users, your model doesn’t accidentally start training on it.

Practice problems

A few from the Pagila DVD-rental schema (Postgres port of Sakila):

Resources

• PostgreSQL documentation — postgresql.org/docs — the most thorough free SQL reference.
• Use The Index, Luke — use-the-index-luke.com — Markus Winand’s free book on indexing.
• Mode Analytics SQL tutorial — mode.com/sql-tutorial — interactive intro.
• SQLBolt — sqlbolt.com — short browser-based lessons; lessons 1–6 cover this guide.
• Designing Data-Intensive Applications — dataintensive.net — Kleppmann’s canonical book.
• Select Star SQL — selectstarsql.com — interactive practice on real data.