SQL AND, OR, IN, BETWEEN and LIKE

AND, OR, IN, BETWEEN and LIKE

One condition is rarely enough. AND needs both sides to be true, OR needs at least one:

SELECT title FROM songs WHERE genre = 'pop' AND plays > 1000;
SELECT title FROM songs WHERE genre = 'pop' OR genre = 'rock';

Here is the trap. AND is stronger than OR, the same way * beats + in maths. So this:

WHERE genre = 'pop' OR genre = 'rock' AND seconds < 200

means "all pop songs, plus rock songs under 200 seconds". If you meant "short songs that are pop or rock", add brackets: (genre = 'pop' OR genre = 'rock') AND seconds < 200. When you mix AND and OR, just always use brackets. Future you says thanks.

Long OR chains get tiring, so SQL has shortcuts:

genre IN ('pop', 'rock', 'indie') matches any value in the list.
seconds BETWEEN 180 AND 240 is a range, and both ends are included.
NOT flips a condition: genre NOT IN ('pop').

For text patterns there is LIKE, with two wildcards:

% means "any number of characters, even none"
_ means "exactly one character"

WHERE title LIKE 'the%'     -- starts with "the"
WHERE title LIKE '%love%'   -- contains "love" anywhere

In SQLite, LIKE ignores upper and lower case for plain English letters, so '%love%' also finds Love Story. Other databases can be stricter, so don't build your whole personality around that.

Tables in this lesson

songs 10 rows

id	title	artist	genre	seconds	plays
1	Lovesick Robot	The Toasters	pop	198	5400
2	Homework Can Wait	Lofi Owl	lofi	152	12000
3	Thunder Lunchbox	Gravel	rock	254	870
4	Love You Like WiFi	Kai Neon	pop	231	9100
5	The Final Boss	Pixel Choir	chiptune	176	3300
6	Monday Again	Gravel	rock	189	2100
7	Rainy Bus Window	Lofi Owl	lofi	245	640
8	Glow Up	Kai Neon	pop	166	15000
9	The Long Goodbye	Velvet Static	indie	302	450
10	Skate Park Sunset	Velvet Static	indie	210	1800

Show the SQL that built them

CREATE TABLE songs (id INTEGER PRIMARY KEY, title TEXT, artist TEXT, genre TEXT, seconds INTEGER, plays INTEGER);
INSERT INTO songs VALUES
 (1, 'Lovesick Robot', 'The Toasters', 'pop', 198, 5400),
 (2, 'Homework Can Wait', 'Lofi Owl', 'lofi', 152, 12000),
 (3, 'Thunder Lunchbox', 'Gravel', 'rock', 254, 870),
 (4, 'Love You Like WiFi', 'Kai Neon', 'pop', 231, 9100),
 (5, 'The Final Boss', 'Pixel Choir', 'chiptune', 176, 3300),
 (6, 'Monday Again', 'Gravel', 'rock', 189, 2100),
 (7, 'Rainy Bus Window', 'Lofi Owl', 'lofi', 245, 640),
 (8, 'Glow Up', 'Kai Neon', 'pop', 166, 15000),
 (9, 'The Long Goodbye', 'Velvet Static', 'indie', 302, 450),
 (10, 'Skate Park Sunset', 'Velvet Static', 'indie', 210, 1800);

Try it yourself

Edit it. Break it. Run it again.

-- Short songs that are pop or rock (brackets matter!)
SELECT title, genre, seconds
FROM songs
WHERE (genre = 'pop' OR genre = 'rock') AND seconds < 200;

Your turn

Type it yourself. That is the whole trick.

1Chill playlist

Return the title and artist of every song whose genre is lofi or indie and that is between 150 and 250 seconds long (including exactly 150 and 250).

SELECT title, artist
FROM songs
WHERE genre IN ('lofi', 'indie');

2Songs that start with The

Return the title of every song whose title starts with the word The.

SELECT title
FROM songs
WHERE title = 'The';