Filtering and Sorting

After this lesson, you will be able to: Filter with multiple conditions: AND/OR/NOT, LIKE, IN, BETWEEN, IS NULL, DISTINCT.

WHERE clauses are 50% of SQL writing. Master the operators here and you'll express almost any business question.

Prerequisites:Basic SQL

Logical operators

Combine conditions.

sql

-- AND / OR / NOT
SELECT * FROM posts WHERE published = TRUE AND user_id = 1;
SELECT * FROM users WHERE name = 'Alex' OR name = 'Sam';
SELECT * FROM posts WHERE NOT published;

-- Precedence: AND binds tighter than OR; ALWAYS parenthesize when mixing
SELECT * FROM posts WHERE published = TRUE AND (user_id = 1 OR user_id = 2);
-- Wrong: WHERE published = TRUE AND user_id = 1 OR user_id = 2
--        Above returns published-by-user-1 PLUS all user_id = 2 rows. Bug.

LIKE + ILIKE for text patterns

Wildcard search. Use full-text search (pl-sql-11) for real search.

sql

-- LIKE: case-sensitive
SELECT * FROM users WHERE email LIKE '%@gmail.com';   -- ends with
SELECT * FROM posts WHERE title LIKE 'How to%';        -- starts with
SELECT * FROM users WHERE name LIKE 'A___';             -- exactly 4 chars starting with A

-- ILIKE: case-insensitive (Postgres-specific)
SELECT * FROM users WHERE email ILIKE '%@GMAIL.COM';

-- LIKE is slow without indexes; for production search use pg_trgm or full-text search

IN, BETWEEN, IS NULL

Common shortcuts.

sql

-- IN, match any of a list
SELECT * FROM users WHERE id IN (1, 2, 3, 4);
SELECT * FROM posts WHERE user_id NOT IN (SELECT id FROM banned_users);

-- BETWEEN, inclusive range
SELECT * FROM posts WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';

-- IS NULL / IS NOT NULL, NULL is NOT equal to NULL
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;
-- WRONG: WHERE deleted_at = NULL, returns 0 rows always

DISTINCT

Deduplicate.

sql

-- Unique emails
SELECT DISTINCT email FROM users;

-- Unique user-post pairs
SELECT DISTINCT user_id, published FROM posts;

-- Postgres extension: DISTINCT ON (column), keep first row per group
SELECT DISTINCT ON (user_id) user_id, title FROM posts
ORDER BY user_id, created_at DESC;
-- Returns each user's most recent post, one row per user

Common mistakes

Comparing to NULL with = (always false). Mixing AND + OR without parentheses (silent wrong results). LIKE '%foo%' on a big table (full scan). Use full-text search or pg_trgm. DISTINCT to 'hide' bad joins (fix the join instead).

←Basic SQL: SELECT, INSERT, UPDATE, DELETE

Back to SQL

JOINs→