AND, OR, NOT, and Operator Precedence

Learning outcomes

Real requirements rarely fit one comparison. Compound predicates combine smaller conditions, and correctness depends on precedence, parentheses, and SQL’s three-valued logic.

The three logical operators

SELECT    customer_id,    full_name,    city,    segmentFROM customerWHERE segment = 'consumer'  AND city = 'Tehran';

Operator precedence

Across mainstream SQL dialects, logical precedence is generally:

-- Interpreted as:-- segment = 'business'-- OR (city = 'Tehran' AND city IS NOT NULL)SELECT customer_id, full_name, city, segmentFROM customerWHERE segment = 'business'   OR city = 'Tehran'  AND city IS NOT NULL;

The city IS NOT NULL condition applies only to the second branch because AND binds more tightly than OR.

Parentheses make business rules visible

-- Requirement A:-- Any business customer, plus Tehran consumers.WHERE segment = 'business'   OR (segment = 'consumer' AND city = 'Tehran')-- Requirement B:-- Customers in Tehran who are either segment.WHERE (segment = 'business' OR segment = 'consumer')  AND city = 'Tehran';

The same words and operators can produce different sets of rows. Group the condition around the requirement, not around visual convenience.

Truth tables for two-valued cases

SQL adds UNKNOWN when NULL participates. In a WHERE clause, both FALSE and UNKNOWN are rejected.

NULL changes apparently simple negation

SELECT customer_id, full_name, cityFROM customerWHERE city <> 'Berlin';

This excludes Berlin rows, but it also excludes rows where city is NULL because the comparison is UNKNOWN.

SELECT customer_id, full_name, cityFROM customerWHERE city <> 'Berlin'   OR city IS NULL;

De Morgan’s laws

Two reliable equivalences help simplify negated groups:

-- Clear direct formWHERE NOT (city = 'Berlin' OR city = 'Tehran')-- Equivalent for non-NULL city valuesWHERE city <> 'Berlin'  AND city <> 'Tehran';

Guard conditions and readable ordering

SELECT    s.sale_id,    s.quantity,    p.unit_price,    s.discount_rateFROM sale AS sJOIN product AS p  ON p.product_id = s.product_idWHERE s.quantity >= 2  AND p.unit_price >= 20  AND s.discount_rate < 0.20;

Place related conditions together and format one major predicate per line. SQL does not guarantee short-circuit evaluation in the same way as many programming languages; do not depend on condition order to prevent an invalid expression.

Build complex filters incrementally

-- Stage 1: all course productsSELECT product_id, product_name, unit_priceFROM productWHERE category = 'course';-- Stage 2: course products in the target price bandSELECT product_id, product_name, unit_priceFROM productWHERE category = 'course'  AND unit_price >= 50  AND unit_price < 90;

Practice lab

1. Return consumers in Berlin or Tehran.
2. Return products that are books or labs and cost less than 30.
3. Return sales with quantity at least 2 unless the discount is 20% or more.
4. Return customers not in Berlin, including customers with an unknown city.
5. Write two differently parenthesized predicates that use the same conditions but return different rows.

SELECT customer_id, full_name, cityFROM customerWHERE segment = 'consumer'  AND (city = 'Berlin' OR city = 'Tehran');SELECT product_id, product_name, category, unit_priceFROM productWHERE (category = 'book' OR category = 'lab')  AND unit_price < 30;SELECT sale_id, quantity, discount_rateFROM saleWHERE quantity >= 2  AND NOT (discount_rate >= 0.20);SELECT customer_id, full_name, cityFROM customerWHERE city <> 'Berlin'   OR city IS NULL;

Common failures

Mixing AND and OR without parentheses

The query may be syntactically valid while expressing a different business rule.

Assuming NOT includes NULL rows

Negating UNKNOWN still produces UNKNOWN.

Depending on left-to-right evaluation

SQL is declarative. Write expressions that are valid independently of optimizer evaluation order.

Changing several conditions before retesting

Compound predicate bugs are easier to isolate incrementally.

Checkpoint

Summary and references

• NOT binds more tightly than AND, which binds more tightly than OR.
• Parentheses document and protect business meaning.
• SQL logical expressions can produce UNKNOWN.
• Test compound filters incrementally and include NULL cases.

References

• SQLite: operators and expression precedence
• PostgreSQL: logical operators
• Microsoft Learn: T-SQL operator precedence