AND, OR, IN, BETWEEN

lesson 2-2 · ~13 min · 4/29

Combining tests

One test is rarely enough. Think of any shop's filter panel: in stock AND under $20 AND rated 4 stars or better. Each checkbox the user ticks becomes one more test in a WHERE clause, so combining tests is everyday SQL. Chain tests with AND and OR:

a AND b keeps a row only if both tests pass.
a OR b keeps a row if at least one passes.

SELECT title FROM movies
WHERE genre = 'scifi' AND year < 2000;

That reads exactly like English: sci-fi movies made before 2000. When you mix AND with OR, add parentheses so the grouping is explicit: WHERE genre = 'scifi' AND (year < 2000 OR rating > 8.5). Without them you are trusting operator precedence, and readers of your query should not have to.

Code exercise · sql

Run it. Both tests must pass, so only the two old sci-fi movies survive.

Two shortcuts: IN and BETWEEN

OR chains on the same column get long. SQL has shortcuts:

genre IN ('animation', 'family') means genre is any value in the list. Same as genre = 'animation' OR genre = 'family'.
year BETWEEN 2015 AND 2016 means year >= 2015 AND year <= 2016. Both ends are included.
Put NOT in front to flip either one: genre NOT IN (...), year NOT BETWEEN ... AND ....

Code exercise · sql

Run it. The IN list matches two genres before the divider. After it, BETWEEN keeps 2015 and 2016, both ends included, so 2017's Paddington 2 is out.

Quiz

Which rows does `WHERE year BETWEEN 2015 AND 2017` keep?

Code exercise · sql

Your turn. Extend the WHERE clause with OR so a movie passes if it is rated above 8 OR was released in 2016 or later. Every movie passes at least one of the two tests, so all five rows should print.

Code exercise · sql

Your turn. Use BETWEEN to print the title and year of every movie released from 1990 through 2016, both years included. Three rows should come back.