Operator precedence and parentheses — Retrieving and Filtering Data

Objective

By the end of this lesson you can predict how PostgreSQL groups a condition that mixes NOT, AND and OR, fix precedence bugs with parentheses, and negate a group of conditions correctly.

Why it matters: A WHERE clause that mixes AND and OR without brackets runs without any error and returns plausible-looking but wrong rows. It is one of the most common bugs in production reports, and one of the most common interview questions.

Dataset: Wadi Market online store. Examples run in the editor on the right.

In arithmetic, 2 + 3 * 4 is 14, not 20: multiplication is done before addition. That rule is called precedence. Logical operators have precedence too, and it is the source of a famous bug.

PostgreSQL evaluates, from first to last:

arithmetic (* and / before + and -)
comparisons (=, <, >=, …)
NOT
AND
OR

So AND binds tighter than OR. A OR B AND C means A OR (B AND C), never (A OR B) AND C. Parentheses override the default, exactly as in arithmetic.

WHERE a AND b OR c       -- means (a AND b) OR c
WHERE a OR b AND c       -- means a OR (b AND c)
WHERE a AND (b OR c)     -- brackets: b OR c first

Without brackets, AND groups first. Write the brackets you mean.

The classic bug

Customer service asks: show app orders that were cancelled or returned. The natural first attempt is:

WHERE channel = 'app' AND status = 'cancelled' OR status = 'returned'

Because AND goes first, PostgreSQL reads it as (channel = 'app' AND status = 'cancelled') OR status = 'returned'. A returned order from the website passes the second half of the OR, so it is kept, even though the request was about the app only. No error, no warning: just extra rows.

ExampleWithout brackets: other channels leak in

SELECT DISTINCT channel, status
FROM orders
WHERE channel = 'app' AND status = 'cancelled'
   OR status = 'returned';

DISTINCT shows which channel–status combinations survived the filter. Besides app/cancelled and app/returned, you'll also see web/returned and marketplace/returned: every returned order passed, whatever its channel.

ExampleWith brackets: exactly what was asked

SELECT DISTINCT channel, status
FROM orders
WHERE channel = 'app'
  AND (status = 'cancelled' OR status = 'returned');

The brackets make PostgreSQL evaluate the OR first, then require channel = 'app' for every row. Only two combinations remain: app/cancelled and app/returned.

SELECT *
FROM orders
WHERE amount > 100;

id	city	status	amount
1	Riyadh	delivered	120
2	Jeddah	cancelled	90
3	Riyadh	delivered	60
4	Dammam	delivered	200
5	Jeddah	delivered	75
6	Riyadh	delivered	40
7	Dammam	delivered	30

PostgreSQL tests the condition on each row. Rows where it is true are kept (2 of 7); the others are dropped before anything else happens.

Compare the AND and OR conditions row by row. A mixed condition is just these steps nested: whatever is grouped first decides which rows the outer operator sees.

Quick check

Which rows does WHERE city = 'Riyadh' OR city = 'Jeddah' AND segment = 'business' keep?

NOT in front of a group

NOT binds tighter than AND and OR, so NOT status = 'delivered' OR status = 'cancelled' means (NOT status = 'delivered') OR status = 'cancelled'. To negate a whole group, put it in brackets: NOT (status = 'delivered' OR status = 'cancelled') keeps orders that are neither delivered nor cancelled.

When you push NOT inside the brackets, AND and OR swap places (this is known as De Morgan's law):

NOT (A OR B) is the same as NOT A AND NOT B
NOT (A AND B) is the same as NOT A OR NOT B

"Neither delivered nor cancelled" is therefore status <> 'delivered' AND status <> 'cancelled'. Writing status <> 'delivered' OR status <> 'cancelled' is true for every order, because no status equals both values.

ExampleOpen orders: neither delivered nor cancelled

SELECT DISTINCT status
FROM orders
WHERE NOT (status = 'delivered' OR status = 'cancelled');

The brackets make NOT apply to the whole OR. What remains are the statuses pending, paid, shipped and returned. Rewriting it as status <> 'delivered' AND status <> 'cancelled' gives exactly the same rows.

Now practise

4 exercises on real data, checked in your browser (practice).