Filtering and Sorting

So far you've selected data, built tables and inserted records. Cool. But a real app can have thousands or even millions of rows, and you need to find specific data fast. That's where filtering and sorting come in. They take a giant pile of data and boil it down to exactly the answer you were after.

Filtering with WHERE: The Basics#

The WHERE clause is your main tool for filtering rows. Picture it asking the database a yes or no question about every single row... and only the rows that answer "yes" make it into your results.

-- Find all orders over $50
SELECT * FROM orders WHERE total > 50;
 
-- Find users who signed up today (since midnight)
SELECT * FROM users WHERE created_at >= CURRENT_DATE;
 
-- Find products that are out of stock
SELECT * FROM products WHERE stock_count = 0;

Comparison Operators#

Here's the full list of comparison operators you can use in a WHERE clause.

Pattern Matching with LIKE#

The LIKE operator lets you search text for patterns. It comes with two special wildcards.

• % matches any number of characters (including zero)
• _ matches exactly one character

-- Find names starting with 'A'
SELECT * FROM users WHERE name LIKE 'A%';
 
-- Find emails from gmail
SELECT * FROM users WHERE email LIKE '%@gmail.com';
 
-- Find names that contain 'son'
SELECT * FROM users WHERE name LIKE '%son%';
 
-- Find 5-letter names starting with 'J'
SELECT * FROM users WHERE name LIKE 'J____';

Combining Conditions with AND / OR#

Real queries usually need more than one condition. Use AND when every condition has to be true, and OR when at least one of them has to be true.

-- Users who are active AND over 18 (both must be true)
SELECT * FROM users
WHERE is_active = TRUE AND age >= 18;
 
-- Products that are cheap OR on sale (either works)
SELECT * FROM products
WHERE price < 20 OR on_sale = TRUE;
 
-- Combine AND and OR with parentheses for clarity
SELECT * FROM products
WHERE category = 'electronics'
  AND (price < 100 OR on_sale = TRUE);

Checking for NULL Values#

NULL means "no value" or "unknown" in SQL. Here's the catch... you can't use = to check for it. You have to use IS NULL or IS NOT NULL.

-- Find users who haven't set a phone number
SELECT * FROM users WHERE phone IS NULL;
 
-- Find orders that have been shipped (ship_date is filled in)
SELECT * FROM orders WHERE shipped_at IS NOT NULL;

The IN Operator#

When you want to check whether a value matches anything in a list, use IN instead of chaining a bunch of OR conditions.

-- Instead of this:
SELECT * FROM users WHERE role = 'admin' OR role = 'moderator' OR role = 'editor';
 
-- Write this (much cleaner!):
SELECT * FROM users WHERE role IN ('admin', 'moderator', 'editor');
 
-- Find orders with specific IDs
SELECT * FROM orders WHERE id IN (101, 205, 308);

The BETWEEN Operator#

For ranges, BETWEEN reads a lot cleaner than writing two comparisons.

-- Products priced between $10 and $50 (inclusive)
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
 
-- Ratings from 3 to 5 stars (inclusive)
SELECT * FROM reviews WHERE rating BETWEEN 3 AND 5;

One gotcha with dates. If created_at stores a time too (a TIMESTAMP), then BETWEEN '2024-01-01' AND '2024-01-31' stops at midnight when January 31 starts and misses that whole last day. For date ranges, use "on or after the first day, and before the day after the last day" instead.

-- Every order from January 2024, including all of January 31
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-02-01';

Sorting with ORDER BY#

Use ORDER BY to decide what order your results show up in.

-- Sort alphabetically by name (A to Z)
SELECT * FROM users ORDER BY name ASC;
 
-- Sort by price, highest first
SELECT * FROM products ORDER BY price DESC;
 
-- Sort by multiple columns (category first, then price within each category)
SELECT * FROM products ORDER BY category ASC, price DESC;

• ASC (ascending) is the default, so smallest to largest and A to Z
• DESC (descending) flips it, so largest to smallest and Z to A

Limiting Results with LIMIT and OFFSET#

LIMIT caps how many rows come back. OFFSET skips a certain number of rows before it starts counting.

-- Get the 10 most expensive products
SELECT * FROM products ORDER BY price DESC LIMIT 10;
 
-- Pagination: page 1 (items 1-20)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Pagination: page 2 (items 21-40)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 20;
 
-- Pagination: page 3 (items 41-60)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

SQL Query Execution Order#

Here's one that trips up a LOT of beginners. SQL doesn't run your clauses in the order you typed them. The real order looks like this.

1. FROM picks which table to read from
2. WHERE filters the rows
3. GROUP BY groups whatever rows are left
4. HAVING filters those groups
5. SELECT chooses the columns
6. ORDER BY sorts the results
7. LIMIT caps how many results you get

That's why you can't use a column alias from SELECT inside your WHERE clause. WHERE already ran before SELECT ever showed up!

A Real-World Example#

Let's mash it all together into a realistic online store query.

-- Find the top 5 most expensive electronics that are in stock,
-- excluding items on clearance
SELECT name, price, stock_count
FROM products
WHERE category = 'electronics'
  AND stock_count > 0
  AND on_clearance = FALSE
  AND price BETWEEN 50 AND 2000
ORDER BY price DESC
LIMIT 5;

TL;DR#

• WHERE filters rows using comparison operators (=, >, <, !=, etc.)
• LIKE and ILIKE match text patterns using % (any characters) and _ (one character)
• AND needs every condition to be true, and OR needs at least one
• Use IS NULL / IS NOT NULL to check for missing values
• IN checks against a list of values, and BETWEEN checks a range
• ORDER BY sorts results (ASC for ascending, DESC for descending)
• LIMIT caps results, and OFFSET skips rows for pagination
• SQL runs FROM first, then WHERE, then SELECT, then ORDER BY, then LIMIT

What's Next?#

You've got single tables handled. Real apps have lots of tables that relate to each other though... users have orders, orders have products and products have categories. Next you'll learn how those relationships work and how to pull data from multiple tables at once with JOINs.