BETWEEN and IN

Objectives

By the end of this lesson, you should be able to:

• Match a range of values with BETWEEN
• Match a set of specific values with IN
• Negate either one

💡 Why this matters: “Salary between 60000 and 75000” and “department is Sales or Marketing or Support” are both extremely common questions. BETWEEN and IN say exactly that, without a chain of ANDs and ORs.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier in this module.

BETWEEN: A Range of Values

SELECT first_name, salary FROM employees WHERE salary BETWEEN 60000 AND 75000;

 first_name | salary
------------+---------
 Erin       | 74000.00
 Jordan     | 61000.00
 Maya       | 67000.00
 Sam        | 63000.00
 Alexis     | 71000.00

BETWEEN 60000 AND 75000 is inclusive on both ends, a salary of exactly 60000 or exactly 75000 would match too. It’s shorthand for salary >= 60000 AND salary <= 75000, genuinely equivalent, just easier to read for a range like this.

IN: A Set of Specific Values

SELECT first_name, department FROM employees WHERE department IN ('Sales', 'Marketing');

 first_name | department
------------+-------------
 Jordan     | Sales
 Maya       | Marketing
 Sam        | Sales
 Alexis     | Marketing

This is exactly the OR example from the last lesson, department = 'Sales' OR department = 'Marketing', written more directly. IN scales better too, checking against five or six values with OR gets unwieldy fast, IN ('Sales', 'Marketing', 'Support', 'Legal') doesn’t.

Negating Both

SELECT first_name, department FROM employees WHERE department NOT IN ('Sales', 'Marketing');

 first_name | department
------------+-------------
 Erin       | Engineering
 Priya      | Engineering
 Taylor     | Engineering

NOT BETWEEN and NOT IN work exactly as expected, matching everything the un-negated version would have excluded. Notice Devon, whose department is NULL, still doesn’t appear here either, NOT IN is still a comparison at heart, and NULL doesn’t satisfy it any more than = or != did.

Try It

1. Write a query for every employee with a salary BETWEEN 65000 and 90000.
2. Write a query for every employee whose department is IN ('Engineering', 'Marketing').
3. Write a query using NOT IN for every employee not in ('Engineering', 'Sales').
4. Rewrite WHERE department = 'Sales' OR department = 'Marketing' OR department = 'Support' using IN instead.

Recap

• BETWEEN low AND high matches a range, inclusive on both ends, shorthand for >= low AND <= high.
• IN (value1, value2, ...) matches any of a set of specific values, shorthand for chained ORs.
• NOT BETWEEN and NOT IN negate either one, and, like other comparisons, neither matches a NULL value.

Next lesson: LIKE and ILIKE, matching text by pattern instead of an exact value.