IN, BETWEEN, LIKE and IS NULL

IN: belonging to a list

IN checks whether a value is within a list. It is a compact way to write several OR conditions on the same column:

-- Equivalent to category = 'audio' OR category = 'displays'
SELECT * FROM products
WHERE category IN ('audio', 'displays');

-- Also with NOT IN to exclude
SELECT * FROM products
WHERE category NOT IN ('audio', 'displays');

BETWEEN: a range (inclusive)

BETWEEN a AND b selects values between a and b, including both endpoints:

-- price >= 40 AND price <= 150
SELECT * FROM products
WHERE price BETWEEN 40 AND 150;

LIKE: text patterns

LIKE searches for text that matches a pattern, using two wildcards:

SELECT * FROM products WHERE name LIKE 'A%';    -- starts with A
SELECT * FROM products WHERE name LIKE '%or';   -- ends with "or"
SELECT * FROM products WHERE name LIKE '%cro%'; -- contains "cro"
SELECT * FROM products WHERE name LIKE 'M_u__'; -- M + 1 + u + 2 = "Mouse"

IS NULL / IS NOT NULL

NULL represents the absence of a value and is special: it is not compared with =. To detect it you use IS NULL (or IS NOT NULL):

SELECT * FROM products WHERE stock IS NULL;      -- no stock data
SELECT * FROM products WHERE stock IS NOT NULL;  -- with stock data

Watch out: column = NULL is never true, not even for nulls. Always IS NULL.