SELECT, Filtering, and Sorting

Querying is the everyday SQL skill. This lesson covers the SELECT statement and the clauses around it that filter, sort, and shape results.

The Anatomy of a Query

SELECT column1, column2
FROM table
WHERE condition
ORDER BY column ASC | DESC
LIMIT n OFFSET m;

SQL is read in this logical order:

1. FROM — pick the source table
2. WHERE — filter rows
3. SELECT — choose columns
4. ORDER BY — sort what's left
5. LIMIT/OFFSET — paginate

Even though you write SELECT first, the engine evaluates FROM and WHERE first — which is why you can't reference an aliased column from SELECT in WHERE.

SELECT

SELECT * FROM users;
SELECT id, email FROM users;
SELECT email AS contact_email, created_at FROM users;
SELECT email, EXTRACT(YEAR FROM created_at) AS signup_year FROM users;

Avoid SELECT * in production code. It returns more data than you need and breaks if the table gains columns. List columns explicitly.

WHERE

SELECT * FROM users WHERE created_at > '2024-01-01';

SELECT * FROM orders
WHERE status = 'paid'
  AND amount >= 100
  AND amount < 1000;

SELECT * FROM users WHERE country IN ('US', 'CA', 'UK');

SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM users WHERE email ILIKE '%@example.com';   -- Postgres, case-insensitive

Operators: = != < <= > >=, plus AND, OR, NOT, IN, BETWEEN, LIKE.

% is the SQL wildcard for any string; _ for any single character. Don't anchor a LIKE with a leading % if you want it to use an index — that requires a full table scan.

NULL Is Special

NULL means "unknown". Any comparison with NULL is unknown, not true and not false:

-- This returns NO rows, even those with NULL email
SELECT * FROM users WHERE email = NULL;

-- Use IS NULL / IS NOT NULL
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- COALESCE returns the first non-null
SELECT COALESCE(nickname, name, 'Anonymous') AS display_name FROM users;

Three-valued logic is one of the most common sources of subtle bugs. Whenever a column allows NULL, ask yourself how it should behave in your filter.

ORDER BY

SELECT * FROM posts ORDER BY published_at DESC;
SELECT * FROM users ORDER BY country ASC, created_at DESC;
SELECT * FROM users ORDER BY created_at DESC NULLS LAST;

By default, ASC. Sort by multiple columns by listing them. NULLS FIRST / NULLS LAST controls where NULLs land.

LIMIT and OFFSET

SELECT * FROM posts ORDER BY published_at DESC LIMIT 20;
SELECT * FROM posts ORDER BY published_at DESC LIMIT 20 OFFSET 40;

LIMIT n OFFSET m is the simple way to paginate ("page 3 of 20-per-page"). Beware: OFFSET 10000 still has to scan and discard the first 10 000 rows. For large data sets use cursor pagination:

-- After the previous page, remember the last published_at
SELECT * FROM posts
WHERE published_at < '2024-08-15 10:30:00+00'
ORDER BY published_at DESC
LIMIT 20;

Always pairs with an index on the sort column. Constant-time regardless of how deep you paginate.

DISTINCT

SELECT DISTINCT country FROM users;
SELECT DISTINCT country, plan FROM users;

DISTINCT removes duplicate rows from the result. SELECT DISTINCT col1, col2 dedupes the combination, not column-by-column.

Useful Functions

String

SELECT LOWER(email), UPPER(name), LENGTH(name) FROM users;
SELECT email, SUBSTRING(email FROM 1 FOR 5) FROM users;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

Date / time

SELECT NOW();                                    -- current timestamp with tz
SELECT CURRENT_DATE;
SELECT EXTRACT(YEAR FROM created_at) FROM orders;
SELECT created_at + INTERVAL '7 days' FROM orders;
SELECT DATE_TRUNC('month', created_at) FROM orders;

Conditional

SELECT id, amount,
       CASE
           WHEN amount >= 1000 THEN 'large'
           WHEN amount >= 100  THEN 'medium'
           ELSE 'small'
       END AS bucket
FROM orders;

Aliases

SELECT u.id, u.email
FROM users AS u
WHERE u.created_at > '2024-01-01';

Aliases (AS u) become essential once you join multiple tables. The AS keyword is optional in most dialects.

Comments

-- Single-line comment
/* multi
   line */

Reading Plans

You can ask the database how it intends to run your query:

EXPLAIN SELECT * FROM users WHERE email = 'alex@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alex@example.com';   -- runs it and reports timings

You'll see whether it's doing an index lookup ("Index Scan") or scanning the whole table ("Seq Scan"). We'll go deep on this in the indexes lesson.

Style

• Uppercase keywords (SELECT, FROM, WHERE) by convention — improves readability
• One clause per line for non-trivial queries
• Indent multi-line conditions consistently
• Use named parameters / placeholders, never string concatenation, in application code (covered later)

Pretty queries are debuggable queries.