How do AND, OR and NOT combine, and what happens if I mix them without parentheses?
They combine in a fixed order, and the order is what catches people out. AND binds more tightly than OR.
So if you write WHERE a OR b AND c, the engine reads it as WHERE a OR (b AND c), not as WHERE (a OR b) AND c. The two give different answers, and the one you get is the one that almost nobody wanted.
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
title VARCHAR(160) NOT NULL,
priority INTEGER NOT NULL,
status VARCHAR(20) NOT NULL,
assigned_to INTEGER
);
INSERT INTO tickets (id, title, priority, status, assigned_to) VALUES
(1, 'Login is broken', 1, 'open', NULL),
(2, 'Slow export', 3, 'open', NULL),
(3, 'Typo on the pricing page', 1, 'closed', 7);
-- This means (priority <= 2) OR (status = 'open' AND assigned_to IS NULL).
SELECT id FROM tickets WHERE priority <= 2 OR status = 'open' AND assigned_to IS NULL;
-- This is the same thing, written out.
SELECT id FROM tickets
WHERE priority <= 2 OR (status = 'open' AND assigned_to IS NULL);
So what does the operator NOT do? It reverses whatever it is attached to. It binds more tightly than AND and OR, and it applies to the whole condition that follows it.
There is one more thing about the three of them that is worth knowing. In a chain of AND conditions, the engine is free to stop as soon as one of them is false, because the row cannot be in the answer either way. That is why a long chain of AND conditions can be quick even when the columns have no index on them.
The next question in this topic looks at why people are careful with NOT.