SELECT and WHERE

Read columns and filter rows with conditions, patterns and NULL checks.

SELECT chooses columns; WHERE chooses rows. Combining them lets you pull exactly the data you need, and nothing else.

SELECT * FROM customers;               -- every column
SELECT name, age FROM customers;        -- only some
SELECT name AS customer FROM customers; -- rename with an alias
SELECT DISTINCT country FROM customers; -- remove duplicates

Avoid SELECT * in application code. It fetches more than you need and breaks when columns are added or reordered.

SELECT name FROM customers
WHERE age >= 30;

name
Ada
Grace

Comparison operators: = <> < > <= >= (note that equality is a single =, and not-equal is <> or !=). Combine tests with AND, OR and NOT, and use parentheses to be explicit, since AND binds tighter than OR.

SELECT * FROM customers
WHERE (country = 'UK' OR country = 'US')
  AND age > 30;

SELECT * FROM customers WHERE country IN ('UK', 'US', 'FI');
SELECT * FROM orders    WHERE total BETWEEN 50 AND 150;   -- inclusive
SELECT * FROM customers WHERE name LIKE 'G%';             -- starts with G
SELECT * FROM customers WHERE name LIKE '_da';            -- one char then 'da'

In LIKE, % matches any run of characters and _ matches exactly one. Text values use single quotes; double quotes are for identifiers in standard SQL.

NULL is not a value

NULL means "unknown or missing". Any comparison with NULL is unknown, so WHERE phone = NULL matches nothing. Use IS NULL and IS NOT NULL.

SELECT * FROM customers WHERE phone IS NULL;
SELECT name, COALESCE(phone, 'n/a') AS phone FROM customers;

SELECT id, total
FROM orders
WHERE status = 'paid' AND total > 50;