IN, BETWEEN & Logical Operators

Reviewed & published by Brayan K

Filtering rarely means matching a single value. You want "any of these departments", "salaries in this band", "this OR that, but only if the other thing". IN, BETWEEN, and the logical operators AND/OR/NOT express those richer conditions cleanly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You'll learn to replace long OR chains with IN, select inclusive ranges with BETWEEN, and — crucially — combine conditions without falling into the AND/OR precedence trap.

What You'll Learn

• Match a list of values with IN
• Exclude a list with NOT IN (and its NULL trap)
• Select inclusive ranges with BETWEEN
• Use BETWEEN on numbers, dates, and text
• Combine conditions with AND, OR, NOT
• Why AND/OR precedence needs parentheses

Our Sample Table: employees

A small staff roster with departments, salaries, and hire dates — ideal for lists and ranges.

1. IN — Match Any Value in a List

IN tests whether a column's value appears in a list you supply. It's the clean replacement for a tangle of OR conditions, and it reads almost like English: "department in Sales, Marketing, or Support".

IN is a guest list at the door. "Are you on the list?" If the name is in the set, you're in — no need to ask three separate yes/no questions.

-- The table this lesson uses:
CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       TEXT,
    department TEXT,
    salary     INTEGER,
    hire_date  DATE
);
INSERT INTO employees VALUES
    (1, 'Amélie Dubois', 'Sales',     52000, '2023-03-14'),
    (2, 'Lucas Martin',  'Marketing', 47500, '2024-07-01'),
    (3, 'Mia Chen',      'Support',   39000, '2022-11-23'),
    (4, 'Jonas Weber',   'Finance',   61000, '2021-05-09'),
    (5, 'Ava Johnson',   'HR',        44000, '2025-01-20'),
    (6, 'Pablo Ruiz',    'Legal',     58000, '2020-09-30');

-- IN matches a value against a LIST — cleaner than many ORs
SELECT name, department
FROM employees
WHERE department IN ('Sales', 'Marketing', 'Support');

-- ✅ Expected result:
-- name | department
-- Amélie Dubois | Sales
-- Lucas Martin | Marketing
-- Mia Chen | Support

-- Uses the employees table from the first example.
-- NOT IN flips it — everything EXCEPT the listed values
SELECT name, department
FROM employees
WHERE department NOT IN ('Sales', 'Marketing');

-- ⚠️ Careful: if the list could contain NULL, NOT IN may
-- return zero rows. Keep the list NULL-free.

-- ✅ Expected result:
-- name | department
-- Mia Chen | Support
-- Jonas Weber | Finance
-- Ava Johnson | HR
-- Pablo Ruiz | Legal

2. BETWEEN — Inclusive Ranges

BETWEEN low AND high selects everything from low to high, including both endpoints. It's shorthand for col >= low AND col <= high and works on numbers, dates, and even text.

-- Uses the employees table from the first example.
-- BETWEEN is an INCLUSIVE range (both ends count)
SELECT name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

-- ✅ Expected result:
-- name | salary
-- Amélie Dubois | 52000
-- Jonas Weber | 61000
-- Pablo Ruiz | 58000

-- Uses the employees table from the first example.
-- BETWEEN shines on dates and text too
SELECT name, hire_date
FROM employees
WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31';

-- ✅ Expected result:
-- name | hire_date
-- Lucas Martin | 2024-07-01

Your Turn: pick the list operator

Add the operator that checks a column against a list of values.

-- Uses the employees table from the first example.
-- 🎯 YOUR TURN — fill in the operator, then "Try it Yourself"
-- Goal: list employees in HR, Finance, or Legal.

SELECT name, department
FROM employees
WHERE department ___ ('HR', 'Finance', 'Legal');   -- 👉 the list operator

-- ✅ Expected: only employees in those three departments.

3. AND, OR, NOT & Precedence

You glue conditions together with AND (both must hold) and OR (at least one holds). The catch: AND is evaluated before OR, just like multiplication before addition in maths. When you mix them, parentheses make your intent unambiguous.

-- Uses the employees table from the first example.
-- Combine conditions with AND / OR — mind the precedence!
SELECT name, department, salary
FROM employees
WHERE department = 'Sales'
  AND (salary > 60000 OR hire_date < '2023-01-01');

-- The parentheses are doing real work:
-- "Sales people who EITHER earn over 60k OR are long-tenured."
-- Without them, AND would bind first and change the meaning.

-- ✅ Expected result:
-- name | department | salary

🧩 Reorder Challenge

Unscramble these lines into a query that lists Marketing or Support employees with a salary between 40k and 60k.

-- Uses the employees table from the first example.
-- SCRAMBLED — arrange into a query that lists Marketing or
-- Support employees whose salary is between 40k and 60k.

WHERE department IN ('Marketing', 'Support')
SELECT name, department, salary
AND salary BETWEEN 40000 AND 60000
FROM employees

Why: SELECT then FROM set up the query, and the two filters live in one WHERE joined by AND. IN handles the department list and BETWEEN handles the inclusive salary band. Result: Lucas (48k, Marketing), Mia (52k, Support) and Omar (44k, Support) — three rows.

🧠 Quick Recall

Predict each result, then reveal. Use the employees table.

3 — Omar (44000, inclusive low), Lucas (48000) and Mia (52000, inclusive high). Both endpoints count.

No — "Support" isn't in the list. Only Amélie and Jonas (both Sales) match; there are no HR employees.

As a OR (b AND c) — AND binds tighter than OR. Add parentheses if you actually meant (a OR b) AND c.

Common Errors (and the fix)

• Assuming BETWEEN is exclusive: it includes both ends. For a half-open date range, prefer date >= start AND date < end.
• NOT IN with NULLs: if the list (often from a subquery) contains NULL, NOT IN can return nothing. Filter NULLs out or use NOT EXISTS.
• Missing parentheses: WHERE dept = 'Sales' OR dept = 'HR' AND salary > 60000 doesn't do what it looks like — AND binds first. Wrap the OR group.
• Reversed BETWEEN bounds: BETWEEN 70000 AND 50000 matches zero rows. Low value first, always.

Mini-Challenge: Department + Salary Band

Combine IN and BETWEEN to find Sales/Support staff earning 55k–75k. Expected shape is in the comments.

-- Uses the employees table from the first example.
-- 🎯 MINI-CHALLENGE — uses only this lesson's ideas
-- HR wants Sales OR Support employees earning between 55k and 75k.
--   1. department is one of: Sales, Support  (use IN)
--   2. AND salary is in the 55000–75000 range (use BETWEEN)
--
-- ✅ Expected: rows satisfying BOTH conditions.

-- your query here

🎉 Lesson Complete

• ✅ IN (...) replaces long OR chains with a clean list check
• ✅ NOT IN excludes a list — but watch for NULLs in it
• ✅ BETWEEN x AND y is an inclusive range on both ends
• ✅ BETWEEN works on numbers, dates, and text
• ✅ AND binds tighter than OR — parenthesize mixed logic
• ✅ Next: add conditional logic to results with CASE

Practice quiz

Continue this course

• Previous: LIKE & Wildcards
• Next: CASE Expressions — Add if/then conditional logic to your queries
• Quick reference: SQL cheat sheet › Filtering & Operators

Frequently asked questions

Is BETWEEN inclusive or exclusive?

BETWEEN is inclusive on both ends. price BETWEEN 10 AND 20 matches 10, 20, and everything in between. It's exactly equivalent to price >= 10 AND price <= 20.

How is IN better than a long OR chain?

IN (1, 2, 3) is shorter and clearer than col = 1 OR col = 2 OR col = 3, and the database can often optimize it better. Both return the same rows, but IN scales much more nicely as the list grows.

Why does NOT IN behave strangely with NULL?

If the IN list contains a NULL, NOT IN can return no rows at all because comparisons with NULL yield UNKNOWN, not TRUE. Always make sure your NOT IN list excludes NULLs, or use NOT EXISTS instead.

What's the precedence of AND vs OR?

AND binds tighter than OR, so A OR B AND C reads as A OR (B AND C). When you mix them, add parentheses to make the grouping explicit — it prevents subtle, hard-to-spot bugs.

Does BETWEEN work on dates and text?

Yes. BETWEEN works on any orderable type. order_date BETWEEN '2026-01-01' AND '2026-01-31' selects a date range, and name BETWEEN 'A' AND 'M' selects names alphabetically — just mind that date ranges are inclusive of the end boundary.

Related lessons

• WHERE Clause