SQL AND OR: How Grouping Changes Your Results

SQL conditions with AND and OR, grouping, precedence, and NULL in a statement.

Use parentheses to show which conditions belong together when you combine AND and OR in SQL. An SQL statement with AND can require several tests to pass, while OR offers alternative paths. The grouping determines which rows reach the result.

SQL AND OR conditions can be checked one row at a time: first evaluate the comparisons, then combine their results. Start with a small example. Suppose an orders table contains the following records. The amount is in US dollars; NULL means the amount is missing.

id region status amount
1 East paid 120
2 West paid 120
3 East pending 120
4 West pending 120
5 East paid 80
6 East paid NULL

Imagine that you want paid orders from either region, with an amount of at least $100. Each row must satisfy the status and amount tests, and it must come from one of the two regions. Write the alternatives inside parentheses:

SELECT id, region, status, amount
FROM orders
WHERE (region = 'East' OR region = 'West')
  AND status = 'paid'
  AND amount >= 100;

Rows 1 and 2 pass. Row 3 has the right region and amount but the wrong status. Row 4 also has the wrong status, and row 5 is below the threshold. Row 6 has a missing amount; its comparison produces an unknown result, so it is excluded. The database retains only rows for which the whole condition is true.

SQL AND Statement: Combine Requirements

An SQL AND statement uses AND within a statement, often in its WHERE clause, to join conditions. An AND condition is true only when both of its operands are true. An OR condition is true when at least one operand is true. Each operand can be a comparison or grouped expression.

For example, status = 'paid' AND amount >= 100 requires both tests. The region expression uses OR: either region = 'East' or region = 'West' can satisfy that part. Combining AND and OR in SQL means a row needs a qualifying region as well as both other properties.

Think of an SQL statement using AND as a series of requirements. Adding another requirement can exclude rows that passed the earlier ones. In contrast, adding an alternative with OR can admit rows through another path. In a SQL statement with AND, identify the expression boundaries before predicting the result.

For another SQL statement with AND, try WHERE status = 'paid' AND region = 'East'. Rows 1, 5, and 6 meet both tests. The missing amount does not matter here because the statement never tests it. Remove the region condition and row 2 joins them.

Why precedence matters

SQL AND OR combinations follow a precedence rule: AND binds more tightly than OR. That means this condition:

WHERE region = 'East'
   OR region = 'West' AND status = 'paid'

is grouped as:

WHERE region = 'East'
   OR (region = 'West' AND status = 'paid')

In this SQL expression with AND and OR, the first branch admits every East row, including pending orders. The second admits West rows only when they are paid. On the sample data, rows 1, 2, 3, 5, and 6 pass; row 4 does not. No amount test appears in this condition, so the missing amount in row 6 has no effect.

Now move the parentheses:

WHERE (region = 'East' OR region = 'West')
  AND status = 'paid'

Rows 1, 2, 5, and 6 pass. Row 3 drops out because the paid requirement now applies to both regions. This SQL condition with AND and OR expresses a different rule even though it uses the same individual comparisons.

When you mix AND with OR in SQL, the indentation can suggest a grouping that the database does not use. Add parentheses to show the intended branches.

Parentheses make the intended logic visible to the next person reading the query. They define grouping, not a guaranteed left-to-right evaluation sequence. Avoid relying on one predicate to run first to prevent an error in another; write expressions that are safe for the values they may encounter.

What NULL does to a condition

In SQL, AND and OR also combine results that are unknown. A comparison such as amount >= 100 cannot establish whether a missing amount meets the threshold. It evaluates to UNKNOWN. SQL uses three truth values for these expressions: TRUE, FALSE, and UNKNOWN. A WHERE clause keeps only TRUE; both FALSE and UNKNOWN are filtered out.

The key combinations for SQL AND OR conditions are:

A B A AND B A OR B
TRUE UNKNOWN UNKNOWN TRUE
FALSE UNKNOWN FALSE UNKNOWN
UNKNOWN UNKNOWN UNKNOWN UNKNOWN

Why does FALSE AND UNKNOWN become FALSE? Both parts would have to be true, and one is already false. Why does TRUE OR UNKNOWN become TRUE? One true part is enough, regardless of the missing value. The remaining cases cannot be settled from the known information.

In the first query, row 6 meets the region and status requirements, but amount >= 100 is UNKNOWN. The combined AND condition is therefore UNKNOWN, and WHERE excludes it. A different SQL statement combining AND and OR could retain a row with an unknown comparison if another OR branch is true.

A second SQL AND statement can include paid orders with either an amount of at least $100 or a missing amount:

WHERE status = 'paid'
  AND (amount >= 100 OR amount IS NULL)

On the sample table, this returns rows 1, 2, and 6. Use IS NULL to test for missing values. amount = NULL yields UNKNOWN, including when amount is NULL, so it does not find those rows. Similarly, amount <> NULL does not find nonmissing values; use IS NOT NULL for that test.

Consider row 3 in this last example: its amount meets the threshold, but its status is pending. The expression inside the parentheses is TRUE, while the status comparison is FALSE. Combining them with AND yields FALSE, so the row is excluded. Row 6 follows the other path: its amount comparison is UNKNOWN, but amount IS NULL is TRUE. The OR group is therefore TRUE, and its paid status lets it pass.

A SQL statement with an AND condition can also join two parenthesized groups of alternatives.

Check a mixed condition before running it

For SQL AND OR conditions, first state the rule in plain language: which requirements apply to every row, and which choices are alternatives? Then group each set of alternatives with parentheses and connect it to the shared requirements. In an SQL query using AND and OR, look for any unparenthesized OR branch that might bypass a requirement.

Before running an SQL statement that uses AND, verify that each requirement is attached to the branch it is meant to restrict. For SQL expressions combining AND with OR, trace both a row that matches an alternative and one that does not.

Finally, test representative rows: one that should pass, one that fails each requirement, and one with NULL in a relevant column. Work through the comparisons and combine their truth values. If the rows returned differ from the rule you wrote, adjust the grouping or the NULL handling and test again.