IN, BETWEEN and LIKE

Real requests rarely name a single value. They ask for a list of makes, a range of mileage or a family of names, and these three operators cover them.

How it works

Each operator is a shorter way to write a group of conditions. IN matches any value from a list, BETWEEN matches a range and LIKE matches a text pattern. They all sit in WHERE and keep the rows that match.

Common mistake

make LIKE 'T' has no %, so it matches only a make that is exactly the letter T. It returns no rows and still runs without an error. Put a % wherever the text may continue.

Worked example

SELECT vehicle_id, make, completed_date
FROM `learn.mot.fact_mot_test`
WHERE make IN ('SKODA', 'SEAT')
  AND test_result = 'FAILED'
  AND completed_date BETWEEN '2024-02-01' AND '2024-03-31'

IN keeps the tests of either make. The other two conditions keep the failed ones in February and March.

The result has one row per test, so a vehicle that failed twice appears twice.

All BigQuery lessons