Hack University - AND, OR, IN, BETWEEN

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.

Requiring two conditions with AND

AND demands that both tests pass on the same row, so this query keeps only the sci-fi movies that are also older than 2000. Two of the five qualify.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

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

Output

The Matrix
Alien

Arrival is sci-fi but too recent, and Inside Out is old enough by nothing else, so both fail. Each additional AND can only ever shrink the result, because a row now has one more hurdle to clear.

The query is split across two lines purely for readability. SQL ignores line breaks and treats everything up to the semicolon as one statement, and putting the WHERE clause on its own line is the normal convention once a query grows.

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 ....

IN and BETWEEN in one script

Two queries run here, with a SELECT '---' between them purely to draw a divider in the output so you can tell the two result sets apart.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title FROM movies WHERE genre IN ('animation', 'family');
SELECT '---';
SELECT title, year FROM movies WHERE year BETWEEN 2015 AND 2016;

Output

Inside Out
Paddington 2
---
Inside Out|2015
Arrival|2016

The IN list is a compact way to write several OR tests. genre IN ('animation', 'family') means exactly the same thing as genre = 'animation' OR genre = 'family', and it stays readable when the list grows to ten values.

BETWEEN 2015 AND 2016 includes both endpoints, so 2015 and 2016 both qualify while Paddington 2 at 2017 falls just outside.

BETWEEN includes both endpoints

WHERE year BETWEEN 2015 AND 2017 keeps the years 2015, 2016, and 2017.

BETWEEN is inclusive on both ends. It expands to year >= 2015 AND year <= 2017, so both boundary years qualify along with everything in the middle.

When you need to exclude an endpoint, BETWEEN cannot express it. Write the comparisons out yourself, as in year >= 2015 AND year < 2017. This matters most with dates and timestamps, where a range that accidentally includes the final midnight double-counts a day.

Accepting either condition with OR

OR passes a row when at least one test succeeds. Here a movie qualifies by being rated above 8 or by being released in 2016 or later, and every movie in the table satisfies one of those, so all five come back.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title, rating FROM movies
WHERE rating > 8 OR year >= 2016;

Output

Inside Out|8.1
The Matrix|8.7
Arrival|7.9
Paddington 2|7.8
Alien|8.5

Inside Out (8.1), The Matrix (8.7), and Alien (8.5) get in on the rating test. Arrival (2016) and Paddington 2 (2017) get in on the year test even though their ratings are below 8. Between them the two conditions cover the whole table.

This is the mirror image of AND. Every extra OR can only ever grow the result, since a row gains one more way to qualify.

A range query with BETWEEN

Asking for movies released from 1990 through 2016, with both years counted, is a textbook BETWEEN job.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title, year FROM movies WHERE year BETWEEN 1990 AND 2016;

Output

Inside Out|2015
The Matrix|1999
Arrival|2016

The pattern is column BETWEEN low AND high, and the low value must come first. Writing the bounds backwards as BETWEEN 2016 AND 1990 matches nothing at all, because it expands to a condition no number can satisfy.

Both ends count, which is what lets Arrival at 2016 in. Alien at 1979 falls below the range and Paddington 2 at 2017 falls above it, leaving three rows.