IN, BETWEEN, LIKE and NULL

You can do a lot with = and >, but SQL has four more tools that make conditions short and precise.

IN — one of a list

Instead of several ORs:

SELECT first_name, last_name, city
FROM students
WHERE city IN ('Buxoro', 'Navoiy', 'Urganch');

This is the same as city = 'Buxoro' OR city = 'Navoiy' OR city = 'Urganch', just shorter. The opposite is NOT IN.

BETWEEN — within a range

SELECT first_name, last_name, score
FROM grades JOIN students ON students.id = grades.student_id
WHERE score BETWEEN 90 AND 100;

BETWEEN a AND b includes both ends, so it means >= a AND <= b. It works for dates too:

SELECT first_name, last_name, birth_date FROM students
WHERE birth_date BETWEEN '2006-01-01' AND '2006-03-31';

LIKE — matching a pattern

• % — any number of characters (including none);
• _ — exactly one character.

SELECT first_name, last_name FROM students WHERE last_name LIKE 'Ra%';

In PostgreSQL LIKE is case-sensitive: 'a%' will not find “Aziz”. For a case-insensitive search there is ILIKE: WHERE first_name ILIKE 'a%'.

NULL — no value

NULL means “unknown” or “not filled in”. For example, some students have no phone number on record. NULL equals nothing — not even itself! That is why = NULL does not work:

SELECT first_name, last_name FROM students WHERE phone IS NULL;

The right way is IS NULL and IS NOT NULL.

SELECT name FROM study_groups WHERE curator_id IS NOT NULL LIMIT 5;

Remember

• IN (…) — one of a list; BETWEEN a AND b — a range including both ends.
• LIKE: % — any characters, _ — one character.
• Only IS NULL / IS NOT NULL work with NULL.