IN and LIKE

The conditions in Lesson 2 checked one thing at a time. IN and LIKE let a single condition say more.

name             station  price
Bifteki          grill    18
Baklava          pastry   7
Melitzanosalata  salad    16
Loukoumades      pastry   9
Souvlaki         grill    11
Dakos            salad    14

IN checks a column against a list. This keeps every dish whose station appears in the list. It is exactly the same as writing station = 'grill' OR station = 'pastry', only shorter and easier to read as the list grows.

SELECT name
FROM dishes
WHERE station IN ('grill', 'pastry');

name
Bifteki
Baklava
Loukoumades
Souvlaki

Numbers work too, like price IN (7, 9, 11). And NOT IN flips it: station NOT IN ('salad') keeps everything that is not from the salad section.

LIKE matches text against a pattern. A % stands in for any run of characters, none included. So 'B%' means the name starts with a B.

SELECT name
FROM dishes
WHERE name LIKE 'B%';

name
Bifteki
Baklava

Move the %, or use two, to match the end or the middle. '%e%' keeps any dish with an e somewhere in its name.

SELECT name
FROM dishes
WHERE name LIKE '%e%';

name
Bifteki
Melitzanosalata
Loukoumades

A single _ matches exactly one character, for when the length matters. NOT LIKE works the way you would expect.

BETWEEN checks a value against a range, with the range being inclusive. This means that price BETWEEN 9 AND 15 keeps everything from 9 up to 15, including 9 and 15 themselves. It is the same as writing price >= 9 AND price <= 15, only shorter.

SELECT name, price
FROM dishes
WHERE price BETWEEN 9 AND 15;

name         price
Loukoumades  9
Souvlaki     11
Dakos        14

It works on dates too, and NOT BETWEEN flips it to keep everything outside the range.

IN, LIKE and BETWEEN are still just used in combination with WHERE. They pick which rows you get and hand back the same dishes table with fewer rows in it.