IN, BETWEEN & Logical Operators

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.

Learn IN, BETWEEN & Logical Operators in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a quick…

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.

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

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.

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.

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

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.

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

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.

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 .

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

Practice quiz

Is BETWEEN inclusive or exclusive of its endpoints?

  • Exclusive on both ends
  • Inclusive low, exclusive high
  • Inclusive on both ends
  • Exclusive low, inclusive high

Answer: Inclusive on both ends. BETWEEN 10 AND 20 matches 10, 20, and everything between — equivalent to >= 10 AND <= 20.

price BETWEEN 10 AND 20 is exactly equivalent to which expression?

  • price >= 10 AND price <= 20
  • price > 10 AND price < 20
  • price >= 10 OR price <= 20
  • price = 10 OR price = 20

Answer: price >= 10 AND price <= 20. BETWEEN is inclusive shorthand for >= low AND <= high.

Why use IN (1, 2, 3) instead of a long OR chain?

  • It returns different rows
  • It is the only way to match a list
  • OR is not valid SQL
  • It's shorter, clearer, and often optimized better

Answer: It's shorter, clearer, and often optimized better. IN and the OR chain return the same rows, but IN scales and reads far better.

What does WHERE department NOT IN ('Sales', 'Marketing') return?

  • Rows in Sales or Marketing
  • Rows whose department is none of the listed values
  • All rows
  • No rows ever

Answer: Rows whose department is none of the listed values. NOT IN keeps everything except the listed values.

Why can NOT IN return zero rows unexpectedly?

  • A NULL in the list makes comparisons yield UNKNOWN
  • NOT IN is not valid SQL
  • It always matches everything
  • Lists can hold only numbers

Answer: A NULL in the list makes comparisons yield UNKNOWN. If the IN list contains NULL, NOT IN can return nothing; keep the list NULL-free or use NOT EXISTS.

Which binds tighter: AND or OR?

  • OR binds tighter than AND
  • They have equal precedence
  • AND binds tighter than OR
  • It depends on the database

Answer: AND binds tighter than OR. AND binds tighter, so A OR B AND C reads as A OR (B AND C) — like multiplication before addition.

How does WHERE a OR b AND c group by default?

  • (a OR b) AND c
  • a OR (b AND c)
  • (a OR b AND c) as one unit
  • a AND (b OR c)

Answer: a OR (b AND c). Because AND binds tighter than OR, it groups as a OR (b AND c). Use parentheses to change it.

What does BETWEEN 20 AND 10 (reversed bounds) match?

  • The same as BETWEEN 10 AND 20
  • Only the value 15
  • Everything in the table
  • Nothing — the low value must come first

Answer: Nothing — the low value must come first. BETWEEN expects the lower bound first; reversed bounds match zero rows.

Does BETWEEN work on dates and text, not just numbers?

  • No — numbers only
  • Yes — it works on any orderable type
  • Only on dates
  • Only on text

Answer: Yes — it works on any orderable type. BETWEEN works on any orderable type, e.g. name BETWEEN 'A' AND 'M'.

For a precise date range on timestamps, what is often safer than BETWEEN?

  • date BETWEEN end AND start
  • date = start OR date = end
  • date >= start AND date < end (half-open)
  • NOT BETWEEN

Answer: date >= start AND date < end (half-open). Because BETWEEN's end is inclusive at midnight, a half-open >= start AND < end avoids missing later-in-the-day times.