SELECT and Filtering

SELECT basics#

SELECT retrieves rows from a table. Using the products table from the introduction:

SELECT name, price FROM products;

SELECT * FROM products;

* selects every column. It's convenient while exploring data interactively, but avoid it in application code — an explicit column list keeps queries stable if the table gains new columns later, and avoids fetching data you don't need.

Filtering with WHERE#

WHERE filters rows to only those matching a condition, evaluated per row before the results are returned:

SELECT name, price FROM products WHERE category = 'Electronics';

Comparison operators: =, != (or <>), <, >, <=, >=.

SELECT name, price FROM products WHERE price > 50;

Logical operators: AND, OR, NOT combine multiple conditions.

SELECT name FROM products
WHERE category = 'Electronics' AND price < 30;

SELECT name FROM orders
WHERE status = 'pending' OR status = 'processing';

Parentheses matter once AND and OR mix — AND binds tighter than OR, so wrap conditions explicitly rather than relying on operator precedence:

-- Explicit and unambiguous
SELECT * FROM orders
WHERE status = 'shipped' AND (quantity > 5 OR user_id = 1);

LIKE — pattern matching#

LIKE matches text against a pattern using two wildcards: % (any number of characters, including zero) and _ (exactly one character).

SELECT name FROM users WHERE email LIKE '%@gmail.com';   -- ends with @gmail.com
SELECT name FROM products WHERE name LIKE 'Wireless%';    -- starts with Wireless
SELECT name FROM products WHERE name LIKE '%board%';      -- contains "board" anywhere

LIKE is case-insensitive by default in MySQL (depending on the column's collation) but case-sensitive in PostgreSQL, where ILIKE gives you a case-insensitive match instead.

IN — matching against a list#

IN checks whether a value matches any item in a list, replacing a chain of OR conditions:

-- These two are equivalent
SELECT name FROM users WHERE country = 'CA' OR country = 'SN' OR country = 'FR';
SELECT name FROM users WHERE country IN ('CA', 'SN', 'FR');

NOT IN excludes the list instead. Be careful combining NOT IN with a subquery that might return NULL — see the "NULL and comparisons" note below.

BETWEEN — matching a range#

BETWEEN matches an inclusive range and reads more naturally than two comparisons chained together:

-- Equivalent to: price >= 20 AND price <= 100
SELECT name, price FROM products WHERE price BETWEEN 20 AND 100;

SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

ORDER BY — sorting results#

ORDER BY sorts the result set. ASC (ascending) is the default; DESC sorts descending. You can sort by multiple columns, and later columns only break ties left by earlier ones:

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

That query groups rows by category alphabetically, and within each category lists the most expensive product first.

LIMIT — capping the result set#

LIMIT restricts how many rows come back — essential for pagination and for avoiding accidentally pulling millions of rows to a client:

SELECT name, price FROM products ORDER BY price DESC LIMIT 5;

Combine it with OFFSET to page through results:

SELECT name, price FROM products
ORDER BY id
LIMIT 10 OFFSET 20;   -- rows 21-30

NULL and comparisons#

NULL means "no value," and it doesn't behave like other values in comparisons. NULL = NULL evaluates to NULL (neither true nor false), not TRUE — so WHERE column = NULL never matches any row, even rows where the column actually is NULL. Use IS NULL / IS NOT NULL instead:

-- Wrong: matches nothing, even if country really is NULL
SELECT * FROM users WHERE country = NULL;

-- Correct
SELECT * FROM users WHERE country IS NULL;
SELECT * FROM users WHERE country IS NOT NULL;

Putting it together#

SELECT name, price, category
FROM products
WHERE category = 'Electronics'
  AND price BETWEEN 20 AND 100
  AND name LIKE '%Keyboard%'
ORDER BY price DESC
LIMIT 10;

Common mistakes#

• Writing WHERE column = NULL expecting it to match null rows — it silently returns zero rows instead of an error, which makes the bug easy to miss in testing.
• Using SELECT * in application code that later breaks (or silently pulls unexpected columns) when the table's structure changes.
• Forgetting ORDER BY before LIMIT. Without an explicit sort, the database is free to return rows in any order it finds convenient, so "the first 10 rows" isn't reliably the same 10 rows across runs.
• Chaining OR conditions instead of IN for many values — functionally equivalent, but IN is shorter and clearer once you're past two or three values.