Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 125 ยท Querying Data

AND, OR and NOT

Combine SQL conditions with AND, OR and NOT. Learn the truth tables, why AND binds tighter than OR, how parentheses fix it, and how NULL affects logic.

A single comparison rarely describes exactly the rows you want. "Outdoor products under 40 euros" has two conditions. "Orders that are pending or shipped" has two alternatives. "Employees outside Engineering" is the opposite of a condition. SQL builds these filters from three logical operators: AND, OR and NOT.

They look simple, and they mostly are. But mixing AND and OR without parentheses produces one of the most common bugs in SQL, one that returns plausible-looking but wrong results with no error at all. This lesson shows each operator, the precedence rule behind that bug, and how missing values (NULL) change the logic. The examples use the shop and company databases.

AND: every condition must be true

AND joins two conditions, and a row passes only if both are true. Each extra AND narrows the result further:

SQL Two conditions with AND ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Category 2 is Outdoor. Of its five products, three cost less than 40. You can chain as many conditions as you need:

SQL Three conditions ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The style used here, one condition per line, each starting with AND, makes long filters easy to read and lets you comment out a single condition with -- while experimenting.

OR: at least one condition must be true

OR keeps a row if either condition is true (or both). Each extra OR widens the result:

SQL Alternatives with OR ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

When every alternative compares the same column with a different value, as here, the IN operator is a shorter way to write it: status IN ('pending', 'shipped'). That's the topic of the next lesson.

OR can also combine conditions on different columns:

SQL OR across columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The result mixes sold-out products with expensive ones. A product that was both would appear once, not twice: WHERE decides for each row whether it's in or out.

NOT: reversing a condition

NOT turns true into false and false into true. Put it before any condition:

SQL NOT ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

For a simple comparison, NOT category_id = 1 means the same as category_id <> 1, and the second form is more common. NOT really shines in front of a whole group of conditions in parentheses, and with operators that have a negative form: NOT IN, NOT BETWEEN, NOT LIKE, IS NOT NULL and NOT EXISTS.

Truth tables

Here's every combination in one place. You can check them yourself: SQLite shows true as 1 and false as 0.

A B A AND B A OR B NOT A
true true true true false
true false false true false
false true false true true
false false false false true
SQL Logic without a table

Press Run (or Ctrl + Enter) to see the result here.

Precedence: AND before OR

When a condition mixes AND and OR, the database doesn't read it left to right. Like multiplication before addition in arithmetic, NOT is applied first, then AND, then OR.

That rule causes a classic bug. Suppose you want employees in Sales (department 3) or Support (department 4) who earn more than 80,000. The natural way to write it is wrong:

SQL The precedence bug ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Look at the results: Quinn Costa (74,000) and Yara Petrov (62,000) are there, even though they earn less than 80,000. Because AND binds tighter, the database read the condition as:

SQL
WHERE department_id = 3
   OR (department_id = 4 AND salary > 80000)

That's "everyone in Sales, plus well-paid people in Support". The salary filter applies to Support only. Parentheses say what you actually mean:

SQL Parentheses fix it ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Now every row earns more than 80,000: six employees instead of eight.

Always use parentheses when you mix AND and OR. Even when the default precedence happens to give the right answer, parentheses make the intent obvious to the next reader. The bug above produces no error, only wrong numbers, which is why it so often reaches reports and dashboards.

The danger is even bigger in an UPDATE or DELETE. DELETE FROM orders WHERE status = 'cancelled' OR status = 'pending' AND customer_id = 5 deletes every cancelled order in the shop, not just customer 5's.

Rewriting with NOT: De Morgan's laws

Two rules, named after the mathematician Augustus De Morgan, tell you how NOT moves through parentheses:

  • 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.

In words: "not (pending or shipped)" means "not pending and not shipped". These two queries return the same rows:

SQL De Morgan in action ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both count 74 orders, the delivered and cancelled ones. A common mistake is to write status <> 'pending' OR status <> 'shipped', which is true for every order, because no order can be both pending and shipped at the same time. Try it: the count jumps to all 120 orders.

Logic with NULL

So far every condition was true or false. But comparing with a missing value, NULL, gives a third result: unknown. SQL's logic therefore has three values, and AND, OR and NOT follow these rules for unknown:

Expression Result Why
true AND unknown unknown It depends on the unknown part
false AND unknown false Already false, whatever the unknown is
true OR unknown true Already true, whatever the unknown is
false OR unknown unknown It depends on the unknown part
NOT unknown unknown The opposite of unknown is still unknown
SQL Three-valued logic

Press Run (or Ctrl + Enter) to see the result here.

WHERE keeps only rows where the condition is true, so rows that evaluate to unknown disappear, even under NOT. In the company database, the CEO and a contractor have no department (department_id is NULL). Watch them vanish from both of these queries:

SQL NOT doesn't bring back NULL rows ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

10 employees are in Engineering and 24 are not, but there are 36 employees. The two people without a department are neither "in Engineering" nor "not in Engineering", because their comparison is unknown. To include them, ask explicitly: WHERE department_id <> 1 OR department_id IS NULL. You'll get to know this behavior well in Working with NULL.

Order of conditions

The order in which you write conditions joined by AND (or by OR) doesn't change the result: price < 40 AND category_id = 2 returns the same rows as category_id = 2 AND price < 40. It usually doesn't change the speed either, because the database's query planner decides for itself which condition to check first, often starting with one it can answer from an index.

That also means you shouldn't count on short-circuit evaluation, a habit from programming languages where a && b never evaluates b if a is false. SQL makes no such promise. A condition like quantity <> 0 AND total / quantity > 10 may still evaluate the division for rows where quantity is 0. SQLite quietly returns NULL for division by zero, but PostgreSQL and SQL Server raise an error. When one part of a condition guards another, use a CASE expression or NULLIF, which do guarantee their order.

Practice

Find all products that are either in the Audio category (4) or the Fitness category (6), cost less than 100, and are in stock. Use parentheses for the category part. You should get four products.

SQL Your turn ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How does SQL read WHERE a = 1 OR b = 2 AND c = 3?

    AND has higher precedence than OR, so it groups first. Add parentheses to get the other meaning.
  2. 2. Which condition is equivalent to NOT (status = 'pending' OR status = 'shipped')?

    By De Morgan's law, NOT (A OR B) equals NOT A AND NOT B. The OR version is true for every row.
  3. 3. What does SELECT 0 AND NULL; return?

    False AND anything is false, so the unknown value doesn't matter and the result is 0 (false).
  4. 4. A table has 36 employees; 10 have department_id 1 and 2 have a NULL department_id. How many rows does WHERE NOT department_id = 1 return?

    For the two NULL rows the comparison is unknown, and NOT unknown is still unknown, so WHERE drops them. 36 - 10 - 2 = 24.

Summary

  • AND keeps a row only when all conditions are true; OR keeps it when at least one is true; NOT reverses a condition.
  • Precedence is NOT, then AND, then OR. Use parentheses whenever you mix AND and OR; the bug produces wrong rows, not errors.
  • De Morgan's laws: NOT (A OR B) = NOT A AND NOT B, and NOT (A AND B) = NOT A OR NOT B.
  • Comparisons with NULL are unknown. WHERE drops unknown rows, and NOT doesn't bring them back; test for NULL with IS NULL.

Next: IN and BETWEEN, shortcuts for matching lists of values and ranges.