Lesson 4 of 8 · 5 min

Filtering and sorting

WHERE with comparison and logical operators, IN, BETWEEN, LIKE, handling NULL, sorting on several columns, and paging through results.

On this page
  1. Comparisons
  2. AND, OR, NOT
  3. IN and BETWEEN
  4. Pattern matching with LIKE
  5. NULL in conditions
  6. Sorting
  7. Paging
  8. Check yourself

Real queries rarely want every row. WHERE keeps the rows you need, and ORDER BY puts them in a useful order.

Comparisons

SELECT name, price FROM products WHERE price < 2000;
SELECT name FROM customers WHERE city = 'Bengaluru';
SELECT id FROM orders WHERE status <> 'cancelled';  -- not equal (!= also works)

Text values go in single quotes. Double quotes are for names of columns or tables.

AND, OR, NOT

SELECT name, price
FROM products
WHERE category = 'Accessories'
  AND price < 3000;

When you mix AND and OR, use brackets. AND binds tighter, which is rarely what you meant:

-- Accessories under 3000, or anything in Video
SELECT name FROM products
WHERE (category = 'Accessories' AND price < 3000)
   OR category = 'Video';

IN and BETWEEN

SELECT id, status FROM orders
WHERE status IN ('pending', 'shipped');

SELECT name, price FROM products
WHERE price BETWEEN 1500 AND 5000;   -- includes both ends

SELECT id FROM orders
WHERE ordered_at BETWEEN '2026-11-01' AND '2026-11-04';

Pattern matching with LIKE

% matches any number of characters, _ matches exactly one:

SELECT name FROM products WHERE name LIKE '%USB%';     -- contains USB
SELECT email FROM customers WHERE email LIKE 'a%';     -- starts with a
SELECT name FROM customers WHERE name LIKE '_eha%';    -- second to fourth letters are "eha"

Case sensitivity depends on the database. MySQL and SQLite usually ignore case for LIKE; PostgreSQL doesn't, and offers ILIKE for case-insensitive matching.

A pattern that starts with % can't use an index, so it scans the whole table. Fine for small tables; for search on large ones, use your database's full-text search.

NULL in conditions

Remember that NULL is "unknown", so any comparison with it is unknown too, and the row is left out:

SELECT name FROM customers WHERE city <> 'Pune';

This returns Asha and Neha, but not Rahul, whose city is NULL. To include him:

SELECT name FROM customers
WHERE city <> 'Pune' OR city IS NULL;

Sorting

SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;

Sort by category A to Z, then within each category from most to least expensive. ASC is the default.

Where NULL values land when sorting differs by database. PostgreSQL lets you choose with NULLS FIRST or NULLS LAST.

Paging

Show results ten at a time:

SELECT id, ordered_at FROM orders
ORDER BY ordered_at DESC, id DESC
LIMIT 10 OFFSET 20;   -- page 3

Always sort when paging; without ORDER BY the order is not guaranteed, and rows can repeat or go missing between pages. On very large tables, OFFSET gets slow because the database still reads the skipped rows. "Keyset" paging is faster: remember the last row you showed and ask for rows after it.

SELECT id, ordered_at FROM orders
WHERE (ordered_at, id) < ('2026-11-03', 102)
ORDER BY ordered_at DESC, id DESC
LIMIT 10;

Check yourself

  1. Find every Accessories product between 1000 and 2500.
  2. Find customers whose email ends with example.com and who live in Bengaluru.
  3. List orders that are not cancelled, newest first.

More from Databases and SQL