SQL / SELECTING ROWS
AND, OR, NOT, and why parentheses save you
Combine WHERE conditions with AND, OR and NOT, predict which rows survive when they mix, and parenthesize to force the grouping you meant.
What you will learn
- Parse a mixed WHERE clause in the right order: NOT, then AND, then OR
- Parenthesize any OR that shares a WHERE clause with an AND, even when it already works
- Tell NOT a AND b apart from NOT (a AND b) and predict which rows differ
- Rewrite NOT (a OR b) as NOT a AND NOT b instead of leaving the OR in place
Understanding AND, OR, NOT, and why parentheses save you
WHERE takes a single boolean expression, and AND, OR and NOT are what let you build that expression out of several comparisons. The three do not have equal standing: NOT binds most tightly, then AND, then OR. So genre = 'scifi' AND price < 20 OR genre = 'history' is parsed as (genre = 'scifi' AND price < 20) OR genre = 'history', which is not the English sentence "scifi or history books under 20". The order you typed the conditions and the line breaks between them change nothing about that grouping.
A workable mental model is that OR splits the clause into independent branches, and a row is returned when at least one branch is satisfied in full. In the query above the branches are "cheap scifi" and "history at any price", which is why a 44-dollar history book comes back: the second branch never mentions price. When a filter returns more rows than you expected, name the branches out loud; the missing condition is almost always in a branch you did not realise you had written.
NOT grabs only the smallest thing next to it, a single comparison, so NOT genre = 'scifi' AND price < 20 means (NOT scifi) AND cheap, not NOT (scifi AND cheap). Pushing a NOT into a group also flips the connective: NOT (a OR b) is equivalent to NOT a AND NOT b, and keeping the OR gives you a condition true for almost every row. One more consequence of three-valued logic: a comparison against NULL yields NULL, NOT NULL is still NULL, and WHERE keeps only TRUE, so a row with NULL in the compared column is missing from a condition and from its negation.
CREATE TABLE books (
title TEXT,
genre TEXT,
price INTEGER
);
INSERT INTO books VALUES
('Tidal Drift', 'scifi', 12),
('Copper Sky', 'scifi', 31),
('Salt and Rope', 'history', 12),
('The Long Ferry', 'history', 44),
('Glasshouse', 'poetry', 9);
-- No parentheses, so SQL groups this as
-- (genre = 'scifi' AND price < 20) OR genre = 'history'
SELECT title, genre, price
FROM books
WHERE genre = 'scifi' AND price < 20 OR genre = 'history';AND is evaluated before OR, so an unparenthesized mix means (AND-group) OR (AND-group) rather than the left-to-right reading you had in mind.
Worked examples
The parentheses you meant to type
Grouping the two genre tests makes the price test apply to both of them.
-- same books table as the main example
SELECT title, genre, price
FROM books
WHERE (genre = 'scifi' OR genre = 'history') AND price < 20;Example explained
Line 1The parentheses turn the OR into one operand of the AND, so price < 20 now guards both genres.
Line 2The Long Ferry at 44 disappears; it only qualified before because the bare OR branch had no price test.
Line 3Copper Sky is still out: its genre matches but 31 < 20 is false, and AND needs both sides true.
NOT one comparison versus NOT a group
Shows the two readings of a NOT next to an AND side by side instead of guessing from missing rows.
-- same books table as the main example
SELECT title,
CASE WHEN NOT genre = 'scifi' AND price < 20 THEN 'kept' ELSE 'dropped' END AS not_first,
CASE WHEN NOT (genre = 'scifi' AND price < 20) THEN 'kept' ELSE 'dropped' END AS not_group
FROM books;Example explained
Line 1not_first negates only genre = 'scifi', so it means "not scifi and cheap" and keeps just two rows.
Line 2not_group negates the whole AND, so it means "not a cheap scifi book" and keeps everything except Tidal Drift.
Line 3Copper Sky is the row that separates the readings: expensive scifi fails the AND, so negating the AND lets it in.
Line 4Putting a condition in the SELECT list as a CASE column is the fastest way to see its truth value per row.
Negating an OR flips it to AND
Confirms that NOT (a OR b) and NOT a AND NOT b agree row by row.
-- same books table as the main example
SELECT title,
CASE WHEN NOT (genre = 'scifi' OR genre = 'poetry') THEN 'y' ELSE 'n' END AS negated_or,
CASE WHEN genre <> 'scifi' AND genre <> 'poetry' THEN 'y' ELSE 'n' END AS anded_negations
FROM books;Example explained
Line 1Both columns agree on all five rows, which is the check to run before trusting a hand-rewritten negation.
Line 2The tempting wrong rewrite is genre <> 'scifi' OR genre <> 'poetry': no book is both genres at once, so that is true for every row and filters nothing.
Line 3Only the two history rows survive, because they are the rows where neither half of the original OR was true.
Line 4If a row had a NULL genre, both columns would read n, since NULL comparisons stay NULL through the negation.
Important notes
Precedence is fixed at NOT, then AND, then OR. Parentheses are the only way to change it; indentation, line breaks and the order you typed the conditions have no effect.
Parentheses group operands, they do not promise evaluation order. SQL may test the second half of an AND first, so qty <> 0 AND total / qty > 2 can still raise a division error; use CASE when you need a real guard.
Common mistakes
Writing WHERE genre = 'scifi' OR genre = 'history' AND price < 20 and expecting price to filter both genres: AND groups first, so every scifi book is returned at any price.
Excluding two values with WHERE genre <> 'scifi' OR genre <> 'poetry': each row satisfies at least one half, so the query returns the whole table instead of filtering it.
Expecting NOT to rescue NULLs: NOT (genre = 'scifi') is NULL for a row with NULL genre, and WHERE keeps only TRUE, so that row is absent from both the condition and its negation.
Try it yourself
Change, predict, then run
On the books table, write one query returning scifi or poetry books under 20 plus every history book at any price, so you get four rows. Then delete the inner parentheses, re-run it, and identify which extra book appears and why.
Open the SQL workspaceCheck your understanding
WHERE a = 1 OR b = 2 AND c = 3 and WHERE (a = 1 OR b = 2) AND c = 3 disagree about exactly one kind of row. Which kind?
- Rows where a = 1 but c <> 3
- Rows where b = 2 and c = 3
- Rows where a = 1 and b = 2 and c = 3
- Rows where none of the three comparisons is true
Show answer
The first clause is parsed as a = 1 OR (b = 2 AND c = 3), so a = 1 on its own is a complete qualifying branch and c is never consulted; the parenthesized version requires c = 3 of every row, so it drops those rows. Option 1 looks like the victim because b = 2 seems to have lost its partner, but a row with b = 2 and c = 3 satisfies both forms.