Level 3: Combining conditions

Lesson

A filter often has several criteria. AND links two conditions that must both be true: genre = 'Novel' AND pages > 300 keeps the novels with more than 300 pages. OR is happy with one of the two: dest = 'MAD' OR dest = 'LIS' keeps the flights to Madrid and those to Lisbon. NOT reverses a condition.

When you mix AND and OR, AND is evaluated first, just like multiplication before addition. a = 1 OR a = 2 AND b = 3 is therefore read as a = 1 OR (a = 2 AND b = 3). As soon as a query mixes the two, add parentheses: they make your intention explicit and avoid silent mistakes.

Two shortcuts make filters easier to read. IN tests membership of a list: dest IN ('MAD', 'LIS') replaces the chain of OR, and NOT IN does the opposite. BETWEEN a AND b keeps the values between a and b, bounds included: pages BETWEEN 200 AND 300 is the same as pages >= 200 AND pages <= 300.

LIKE looks for a pattern in text. The % sign stands for any sequence of characters, even an empty one, and _ stands for exactly one character. email LIKE '%@mail.com' finds the addresses that end with @mail.com; name LIKE 'A%' finds the names that start with A; name LIKE '____' (four _) finds the names of exactly four letters.

Finally, an empty cell contains NULL, which means “unknown value”. NULL is equal to nothing, not even to itself: WHERE email = NULL never returns any row. To test for an empty cell, you write IS NULL, and IS NOT NULL for a filled one. Level 12 explains why in detail.

Syntax

SELECT columns
FROM my_table
WHERE (a = 1 OR a = 2) AND b BETWEEN 10 AND 20 AND c LIKE 'D%' AND d IS NULL;

Worked example

SELECT title, genre, pages
FROM books
WHERE genre = 'Novel' AND pages > 300;

Both conditions must be true: a novel AND more than 300 pages.

Example result

Key points

• AND: both conditions; OR: at least one.
• IN (list), BETWEEN (ends included), LIKE with % and _.
• NULL is tested with IS NULL, never with = NULL.

Common pitfalls

• a = 1 OR a = 2 AND b = 3 reads as a = 1 OR (a = 2 AND b = 3): add parentheses.
• WHERE email = NULL never returns anything.

SQLite specifics

In SQLite, LIKE doesn't distinguish upper and lower case (for letters without accents): name LIKE 'alice%' finds “Alice Martin”. The = sign does distinguish them. In PostgreSQL, LIKE is case-sensitive and ILIKE is the one that ignores case.

The level’s 5 exercises

1. Guided · Show the title and page count of the novels (genre 'Novel') with fewer than 350 pages. (Table: books)
2. Practice · Show the airline and destination of the flights going to 'MAD', 'LIS' or 'FCO'. (Table: flights)
3. Practice · Show the title and page count of the books that have between 200 and 300 pages (both included). (Table: books)
4. Practice · Show the name and email address of the contacts whose address ends with '@mail.com'. (Table: contacts)
5. Challenge · Show the loans not returned yet (empty return_date) of members 3 and 4: loan id, member and loan date. (Table: loans)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic