SELECT and Filtering
WHERE, ORDER BY, LIMIT, OFFSET, ILIKE, and pattern matching
Skills taught: pg.select, pg.where-clauses, pg.ilike, pg.pagination, pg.null-handling
SELECT fundamentals
-- Select all columns
SELECT * FROM users;
-- Select specific columns with aliases
SELECT
id,
email,
created_at AS registered_at
FROM users;
-- Filter with WHERE
SELECT * FROM users
WHERE is_active = TRUE
AND created_at > '2026-01-01'
AND role IN ('admin', 'moderator');
-- NOT IN
SELECT * FROM users
WHERE role NOT IN ('banned', 'suspended');
-- BETWEEN (inclusive on both ends)
SELECT * FROM orders
WHERE total_price BETWEEN 10.00 AND 100.00;
-- Sort and paginate
SELECT * FROM posts
WHERE published = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 40; -- page 3 (0-indexed), 20 per page
NULL — the three-valued logic trap
-- NULL comparisons always return NULL (not TRUE or FALSE)
SELECT * FROM users WHERE deleted_at = NULL; -- returns 0 rows! (wrong)
SELECT * FROM users WHERE deleted_at IS NULL; -- correct
SELECT * FROM users WHERE deleted_at IS NOT NULL;
-- COALESCE returns the first non-null value
SELECT
id,
COALESCE(display_name, username, 'Anonymous') AS name
FROM users;
-- NULLIF returns NULL if two values are equal (useful to avoid divide-by-zero)
SELECT total_revenue / NULLIF(order_count, 0) AS avg_order_value
FROM sales_summary;
Text search: LIKE and ILIKE
-- LIKE is case-sensitive
SELECT * FROM products WHERE name LIKE 'Apple%'; -- starts with 'Apple'
SELECT * FROM products WHERE name LIKE '%phone%'; -- contains 'phone'
SELECT * FROM products WHERE name LIKE '_hone'; -- any char then 'hone'
-- ILIKE is case-insensitive (PostgreSQL extension)
SELECT * FROM products WHERE name ILIKE '%iphone%'; -- matches 'iPhone', 'IPHONE', 'iphone'
-- % matches zero or more characters
-- _ matches exactly one character
-- For full-text search on large tables, use tsvector/tsquery (not ILIKE)
SELECT * FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'postgresql & performance');
Tip: Pagination with OFFSET is slow at large offsets. OFFSET 10000 makes Postgres read 10001 rows and discard 10000. For 'load more' or infinite scroll: use WHERE id > :last_id ORDER BY id LIMIT 20 (keyset pagination). For page numbers, OFFSET is fine up to a few thousand rows.
Frequently Asked Questions
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY. Example: WHERE age > 18 removes individual rows; HAVING COUNT(*) > 5 removes groups with fewer than 5 rows. You cannot use aggregate functions (COUNT, SUM) in WHERE — that is what HAVING is for.