Filtering Deeper: AND, OR, IN, BETWEEN, LIKE, NULL

Real questions rarely have a single condition. "Engineers in Antwerp earning over €60k" is three conditions at once. This lesson is the toolkit for combining them, plus the one value that breaks every beginner's filter: NULL.

In one line

Combine conditions with AND / OR, match a list with IN, a range with BETWEEN, a text pattern with LIKE, and always test missing values with IS NULL, never = NULL.

AND and OR

AND requires every condition to be true; OR requires at least one. Run it: engineers earning over €60k.

Two conditions joined with AND

SELECT name, salary, city
FROM employees
WHERE department = 'Engineering' AND salary > 60000

Editable · runs in your browser

Watch out

When you mix AND and OR, use parentheses. WHERE a = 1 AND b = 2 OR c = 3 does not mean what it looks like. AND binds tighter than OR, so it reads as (a=1 AND b=2) OR c=3. Write the parentheses you mean.

IN — match any value in a list

IN is a clean shorthand for several ORs. WHERE city IN ('Antwerp', 'Ghent') beats WHERE city = 'Antwerp' OR city = 'Ghent'. Run it:

IN matches any value in the list

SELECT name, city
FROM employees
WHERE city IN ('Antwerp', 'Ghent')

Editable · runs in your browser

BETWEEN — match a range

BETWEEN is inclusive on both ends: salary BETWEEN 48000 AND 62000 includes both 48000 and 62000. Rows below 48000 or above 62000 drop out; the endpoints themselves stay:

name	salary
Ada	62000
Bo	48000
Cy	71000
Di	52000
Eli	45000

WHERE salary BETWEEN 48000 AND 62000

name	salary
Ada	62000
Bo	48000
Di	52000

Inclusive range: 48000 and 62000 pass; Cy (71000) and Eli (45000) fall outside and are dropped.

Try it now: write the range filter the visual showed, name and salary of employees earning between 48000 and 62000 (inclusive).

Your turn · Exercise

Return the name and salary of employees whose salary is between 48000 and 62000 (inclusive).

The data

employees

5 rows returned

name	salary
Ada	62,000
Bo	48,000
Cy	71,000
Di	52,000
Eli	45,000

SELECT name, salary
FROM employees
WHERE salary BETWEEN ... AND ...

Runs in your browser

Run it:

BETWEEN includes both endpoints

SELECT name, salary
FROM employees
WHERE salary BETWEEN 48000 AND 62000
ORDER BY salary

Editable · runs in your browser

LIKE — match a text pattern

LIKE matches patterns with two wildcards: % (any number of characters) and _ (exactly one). WHERE name LIKE 'A%' finds names starting with A. Run it, then try '%a%' for names containing a lowercase a:

LIKE with % wildcard — names starting with A

SELECT name, city
FROM employees
WHERE name LIKE 'A%'

Editable · runs in your browser

NULL — the missing value

NULL means "unknown / no value." It is not zero and not an empty string. The trap: WHERE bonus = NULL returns nothing, ever, because nothing "equals" an unknown. You must use IS NULL / IS NOT NULL. Run it to find employees with no recorded bonus:

IS NULL finds missing values; = NULL would return nothing

SELECT name, bonus
FROM employees
WHERE bonus IS NULL

Editable · runs in your browser

Watch out

= NULL and <> NULL always return zero rows, silently. This is the #1 source of "my filter returns nothing and I can't see why." Missing values are tested with IS NULL and IS NOT NULL, never =.

Your turn

Two conditions at once. Return the name and salary of engineers earning more than 60000.

Your turn · Exercise

Return the name and salary of engineers (department Engineering) who earn more than 60000.

The data

employees

5 rows returned

name	department	salary	city
Ada	Engineering	62,000	Antwerp
Bo	Sales	48,000	Ghent
Cy	Engineering	71,000	Antwerp
Di	Sales	52,000	Bruges
Eli	Engineering	45,000	Ghent

SELECT name, salary
FROM employees
WHERE ... AND ...

Runs in your browser

Next: instead of listing rows, you'll start summarising them. Counting, summing, and averaging with GROUP BY.