Combining Conditions with AND, OR, NOT

SQL uses standard boolean algebra with the same precedence rules you would expect from mathematics: NOT binds tightest, then AND, then OR. So a OR b AND c is parsed as a OR (b AND c), not as (a OR b) AND c. That is exactly the reading people get wrong, and it silently returns more rows than intended.

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 page times out',  3, 'open',   11),
  (2, 'Invoice PDF is blank',  2, 'open',   12),
  (3, 'Export times out',     1, 'open',   11),
  (4, 'Dark mode request',    1, 'closed', 12),
  (5, 'Slow search results',  3, 'closed', 11);

-- Reads as: status = 'open' OR (priority = 1 AND assigned_to = 11)
SELECT id, title FROM tickets
WHERE status = 'open' OR priority = 1 AND assigned_to = 11
ORDER BY id;

Three rows come back: tickets 1, 2 and 3. Ticket 2 leaks in because the first branch is TRUE on its own — it is open, regardless of priority or owner. Tickets 4 and 5 are excluded: ticket 4 is priority 1 but assigned to engineer 12, so the AND branch is FALSE, and ticket 5 is neither open nor priority 1.

The intended question was "tickets that are open or urgent, and that belong to engineer 11", which needs the parentheses on the other side: WHERE (status = 'open' OR priority = 1) AND assigned_to = 11. That version returns only tickets 1 and 3 — ticket 2 drops out because engineer 12 owns it, and ticket 4 drops out because it is closed and not engineer 11's.

Rule of thumb: add parentheses whenever you write both AND and OR in the same predicate, even when you are sure of the precedence. It costs nothing and it removes the need for the reader to reconstruct the parse.

NOT is not wrong, it is fragile. The reason is that it inverts the whole predicate, and once you invert a compound condition the NULL semantics become much harder to reason about.

Take WHERE NOT (assigned_to IS NULL). This is well-formed: IS NULL yields TRUE or FALSE, never UNKNOWN, so the negation is exact. But take WHERE NOT (assigned_to = 11) instead. For a row where assigned_to is NULL, the inner comparison is UNKNOWN, and NOT UNKNOWN is still UNKNOWN — so that row is dropped, exactly as it would be by assigned_to = 11. The predicate does not mean "everyone except engineer 11's tickets"; it means "everyone whose assignee is known and is not 11".

The second problem is readability. WHERE NOT (status = 'closed' AND assigned_to IS NULL) forces the reader to distribute the negation across two conditions and remember that De Morgan's law also flips the AND into an OR. WHERE status <> 'closed' OR assigned_to IS NOT NULL says the same thing without the mental gymnastics — though even that needs parentheses to be unambiguous.

A third point is the NULL trap in the negated comparison itself: <> behaves exactly like = when a NULL is involved, so status <> 'closed' silently drops rows whose status is NULL. Rewriting with IS NULL and IS NOT NULL keeps the intent visible.

The practical advice for an interview: prefer positive predicates, and when you must negate, negate a positive expression you have already made NULL-safe. De Morgan's law is the tool for turning NOT into something readable, not a reason to avoid it.

This is the classic compound predicate: an AND that contains an OR. It has to be written with parentheses, because the default precedence would bind the AND to only one branch of the OR.

CREATE TABLE tickets (
  id INTEGER PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  priority INTEGER NOT NULL,
  assigned_to INTEGER
);

INSERT INTO tickets (id, title, priority, assigned_to) VALUES
  (1, 'Login page times out',   1, 11),
  (2, 'Invoice PDF is blank',   1, 12),
  (3, 'Export times out',      1, NULL),
  (4, 'Dark mode request',     3, NULL),
  (5, 'Slow search results',   2, NULL);

SELECT id, title, priority, assigned_to
FROM tickets
WHERE priority = 1
  AND (assigned_to IS NULL OR assigned_to IN (11, 12))
ORDER BY id;

Three rows come back: ticket 1 (assigned to 11), ticket 2 (assigned to 12) and ticket 3 (unassigned). Ticket 4 is high priority but its assignee is NULL and it is priority 3, so the outer AND rejects it; ticket 5 is priority 2.

Two ways this is usually got wrong. Dropping the parentheses gives priority = 1 AND assigned_to IS NULL OR assigned_to IN (11, 12), which parses as (priority = 1 AND assigned_to IS NULL) OR (assigned_to IN (11, 12)) and therefore returns every ticket owned by engineers 11 and 12 regardless of priority — ticket 1, 2, and also ticket 2 if it were low priority.

The second mistake is writing assigned_to = NULL inside the OR branch, which makes that branch UNKNOWN for unassigned rows and silently excludes exactly the tickets the query was supposed to find. IS NULL is the only spelling that matches missing values.

If the OR list grows, swap IN (11, 12) for a join to a team-membership table, or for EXISTS against it, so the set of engineers lives in one place instead of being hard-coded into the query.