SQL online course

SQL AND and OR Operators

Learn how SQL AND and OR operators combine WHERE conditions to filter rows, including precedence, parentheses, NULL handling, and practical examples.

The SQL AND and OR operators combine conditions so a query can filter rows using more than one requirement. They are most commonly used in a WHERE clause.

A condition is also called a predicate. For each row, a predicate or larger boolean expression evaluates to true, false, or unknown. A WHERE clause returns only rows for which the complete expression evaluates to true.

Sample customer data

The examples use a table named customer with these columns:

  • id: integer customer identifier
  • name: customer name
  • address: street address
  • city: text city name
  • state: text state or region code
  • zip: postal code, stored as text or an integer depending on the database design

Customer rows

1 — Bill Smith — Hope — CA

2 — Mary Smith — Harmony — AZ

3 — Bob Smith — Hope — CA

4 — Chang Chao — Hong Kong — CN

5 — John Smith — Toronto — CA

Single quotes are used for text literals in standard SQL, as in city = 'Hope'. Use values that match the column data type.

Using the AND operator

AND returns true only when every connected condition is true. It is useful when a row must satisfy all requirements.

SELECT column_list
FROM table_name
WHERE condition1 AND condition2;

To find customers whose city is Hope and whose state is California, write:

SELECT *
FROM customer
WHERE city = 'Hope' AND state = 'CA';

Customers 1 and 3 satisfy both conditions, so they are returned. Customer 5 has state CA, but its city is Toronto, so it is excluded. A row that matches only one AND condition does not qualify.

Evaluation of city = 'Hope' AND state = 'CA'

Customer 1: city true; state true; AND true; returned

Customer 2: city false; state false; AND false; not returned

Customer 3: city true; state true; AND true; returned

Customer 4: city false; state false; AND false; not returned

Customer 5: city false; state true; AND false; not returned

Using the OR operator

OR returns true when at least one connected condition is true. A row matching both conditions is also returned, but it appears only once in the result.

SELECT column_list
FROM table_name
WHERE condition1 OR condition2;

To find customers who are in Hope or in California, write:

SELECT *
FROM customer
WHERE city = 'Hope' OR state = 'CA';

Customers 1 and 3 match both conditions. Customer 5 matches only the state condition. Customers 2 and 4 match neither condition. Therefore, customers 1, 3, and 5 are returned.

Evaluation of city = 'Hope' OR state = 'CA'

Customer 1: city true; state true; OR true; returned

Customer 2: city false; state false; OR false; not returned

Customer 3: city true; state true; OR true; returned

Customer 4: city false; state false; OR false; not returned

Customer 5: city false; state true; OR true; returned

AND versus OR

Using the same predicates makes the difference clear:

city = 'Hope' AND state = 'CA' — customer IDs 1 and 3. These rows meet both conditions.

city = 'Hope' OR state = 'CA' — customer IDs 1, 3, and 5. IDs 1 and 3 meet both conditions; ID 5 meets only the second condition.

Only the first condition: no sample rows. A city of Hope in a non-CA state would belong here.

Only the second condition: customer 5, because its state is CA but its city is not Hope.

Neither condition: customers 2 and 4.

In general, AND produces a narrower result because every condition is required. OR produces a broader result because any one condition is enough.

Combining AND and OR

A boolean expression can contain both operators. SQL normally evaluates AND before OR. This rule is called operator precedence: the default order in which operators are evaluated.

Suppose the requirement is: customers in California who are in either Hope or Toronto. Group the alternatives with parentheses:

SELECT *
FROM customer
WHERE state = 'CA'
  AND (city = 'Hope' OR city = 'Toronto');

The parentheses make the intended logic explicit: first determine whether the city is Hope or Toronto, then require the state to be CA. Customers 1, 3, and 5 qualify.

Without parentheses, this query has a different logical grouping:

SELECT *
FROM customer
WHERE state = 'CA' AND city = 'Hope'
   OR city = 'Toronto';

Because AND is evaluated first, SQL treats it as:

(state = 'CA' AND city = 'Hope') OR city = 'Toronto'

That expression returns customers in CA who are in Hope, plus every customer in Toronto even if that customer is not in CA. Use parentheses whenever AND and OR appear together. Parentheses are both a correctness tool and a readability aid.

NULL and three-valued logic

NULL marks missing or unknown data. It is not the same as an empty string, zero, or a regular text value.

SQL uses three-valued logic: conditions can be true, false, or unknown. A comparison involving NULL generally produces unknown, not true or false. For example, neither comparison is a valid way to find missing states:

WHERE state = NULL
WHERE state <> NULL

Since a WHERE clause returns only true rows, rows producing unknown are not returned. Use the special NULL predicates instead:

SELECT *
FROM customer
WHERE state IS NULL OR state = 'CA';

This returns rows whose state is missing or whose state is CA. To find rows where a value is present and not CA, use state IS NOT NULL AND state <> 'CA'.

Common filtering practices

  • Use single quotes for standard SQL string literals, such as state = 'CA'.
  • Compare columns with values appropriate to their data types. For example, do not treat a numeric column as text without understanding your database's conversion rules.
  • Use explicit columns in production queries, such as SELECT id, name, city, state, rather than relying on SELECT *. SELECT * is convenient for small demonstrations.
  • Filtering does not guarantee row order. Add an ORDER BY clause when a stable order is required.
  • Keep complex requirements grouped with parentheses and format each condition on its own line.

Troubleshooting AND and OR queries

Too few rows

If a query returns fewer rows than expected, you may have used AND when the requirement was to match either condition. Check whether every predicate must be true or whether just one predicate is sufficient. Use OR for the latter requirement.

Too many rows

If a query returns more rows than expected, OR may be allowing rows that match only one condition. Use AND when all requirements must be satisfied.

Unexpected results from mixed operators

Check SQL precedence: AND is evaluated before OR. Add parentheses around the conditions that belong together.

Missing values are not found

Replace = NULL or <> NULL with IS NULL or IS NOT NULL.

Text syntax errors

Inspect the string delimiters. A standard text comparison looks like city = 'Hope', with single quotes around the value.

Rows appear in a different sequence

A WHERE clause chooses rows but does not define their order. Add ORDER BY id or another suitable ordering expression.

Exam-relevant summary

  • A WHERE clause limits returned rows according to a condition.
  • A predicate is a condition evaluated for each row.
  • AND is true only when all connected conditions are true.
  • OR is true when one or more connected conditions are true.
  • Only true WHERE results are returned; false and unknown results are excluded.
  • AND has higher precedence than OR.
  • Parentheses control grouping and should be used when combining AND and OR.
  • Use IS NULL and IS NOT NULL for NULL checks; do not use equality with NULL.

For related filtering techniques, see the SQL WHERE clause, SQL BETWEEN operator, SQL IN operator, and SQL LIKE operator.