Lists and ranges: IN and BETWEEN

In the previous modules you filtered with WHERE using equalities and comparisons: salary > 30000, department_id = 1, hired_on >= '2020-01-01'. But when the condition becomes "the value must be one of these five", chained ORs pile up:

-- Heavy and fragile: adding a department means touching the query
SELECT first_name, last_name
FROM   employees
WHERE  department_id = 1
   OR  department_id = 2
   OR  department_id = 3;

SQL offers two shortcuts to make this readable.

IN — "one of these"

The IN (...) operator takes a list of values and returns true if the column equals any of them:

SELECT first_name, last_name, department_id
FROM   employees
WHERE  department_id IN (1, 2, 3);

It is semantically identical to the ORs above, but far more readable: it reads naturally as "where department_id is in (1, 2, 3)". It also works with strings and dates:

SELECT name
FROM   departments
WHERE  city IN ('Milano', 'Roma');

There is also NOT IN (...), which does the opposite:

-- Employees who are NOT in Engineering or Sales:
SELECT first_name, last_name
FROM   employees
WHERE  department_id NOT IN (1, 2);

BETWEEN — "between A and B, both inclusive"

When the condition is a continuous range (numbers, dates), instead of writing column >= A AND column <= B you have BETWEEN A AND B:

-- Employees with a salary between 30000 and 50000 (both inclusive):
SELECT first_name, last_name, salary
FROM   employees
WHERE  salary BETWEEN 30000 AND 50000;

BETWEEN is inclusive on both endpoints. It works very nicely with dates too:

-- Everyone hired in 2020:
SELECT first_name, last_name, hired_on
FROM   employees
WHERE  hired_on BETWEEN '2020-01-01' AND '2020-12-31';

Try it

Review exercise