VMware ESXi and vSphere Cluster Management

SQL AND and OR Operators: Filter Rows with Multiple Conditions

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

SQL logical operators let you combine multiple conditions when filtering rows. The two most common logical operators are AND and OR, usually used inside a WHERE clause.

A WHERE clause restricts which rows a query returns. SQL evaluates its conditions for each row. A condition is a Boolean expression, such as city = 'Hope'. In SQL, a condition can evaluate to true, false, or unknown when NULL is involved.

Sample customer table

The examples use a table named customer with customer details:

Using the AND operator

AND requires every connected condition to be true. A row is returned only when it satisfies all the requirements in the WHERE clause.

The general form is:

SELECT column_list
FROM table_name
WHERE condition_1
  AND condition_2;

To find customers whose city is Hope and whose state is California, compare two different columns:

SELECT id, name, city, state
FROM customer
WHERE city = 'Hope'
  AND state = 'CA';

The result contains Bill Smith and Bob Smith. Each of those rows passes both tests: city = 'Hope' and state = 'CA'.

Adding another condition with AND narrows the result set because every returned row must meet one more requirement. For example, this query requires a particular postal code as well:

SELECT id, name, city, state, zip
FROM customer
WHERE city = 'Hope'
  AND state = 'CA'
  AND zip = '98765';

Using the OR operator

OR returns a row when at least one connected condition is true. A row may satisfy both conditions or only one of them.

The general form is:

SELECT column_list
FROM table_name
WHERE condition_1
   OR condition_2;

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

SELECT id, name, city, state
FROM customer
WHERE city = 'Hope'
   OR state = 'CA';

Bill Smith and Bob Smith qualify through both tests. John Smith qualifies through the state test even though his city is Toronto. This OR query returns more rows than either individual condition alone because it accepts either route to qualification.

AND versus OR

For two conditions, AND returns the intersection of the qualifying sets: rows that belong to both sets. OR returns their union: rows that belong to either set.

Using city = 'Hope' as the first condition and state = 'CA' as the second gives this row-by-row comparison:

• Rows 1 and 3 match both conditions, so both AND and OR return them.
• Row 5 matches only the second condition, so OR returns it but AND does not.
• Rows 2 and 4 match neither condition, so neither operator returns them.

Combining AND and OR

Many useful filters combine both operators. For example, suppose the requirement is: customers in California whose city is either Hope or Toronto.

Parentheses group the two city alternatives before the state requirement:

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

This returns Bill Smith, Bob Smith, and John Smith. The grouped logic means:

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

In plain language, the state must be CA, and the city must be one of the two allowed cities.

Operator precedence

Operator precedence is the default order in which SQL evaluates operators. SQL evaluates AND before OR unless parentheses change the grouping.

Consider this ungrouped expression:

WHERE state = 'CA'
  AND city = 'Hope'
   OR city = 'Toronto'

Because AND has higher precedence, SQL interprets it as:

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

That version returns every customer in Hope, California, plus every customer in Toronto regardless of state. It does not enforce the California requirement for Toronto rows.

When the intended meaning is “California and either Hope or Toronto,” use parentheses explicitly:

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

NULL and logical conditions

NULL represents a missing or unknown value. It is not the same as an empty string, zero, or a normal text value.

Comparisons with NULL do not produce true or false in the usual way. For example, this is not a valid way to find missing addresses:

WHERE address = NULL

The expression evaluates to unknown, so a WHERE clause does not retain the row. Use IS NULL instead:

WHERE address IS NULL

Use IS NOT NULL to find rows where a value is present:

WHERE address IS NOT NULL

Unknown values also affect AND and OR. For example, if address is NULL, then address = '123 Main Street' is unknown. An AND expression is retained only when its overall result is true. An OR expression can still be true if another condition is true.

For example, to find California customers whose address is missing or whose city is Hope, use both a NULL test and parentheses:

SELECT id, name, city, state, address
FROM customer
WHERE state = 'CA'
  AND (address IS NULL OR city = 'Hope');

The state condition must be true, and the grouped expression accepts either a missing address or a Hope city.

Writing reliable filters

Use single quotes for text values

Use ordinary single quotes for SQL string literals:

WHERE state = 'CA'
  AND city = 'Hope'

Column names identify stored fields; quoted values such as 'CA' and 'Hope' are literal text values. Do not use typographic quotation marks copied from formatted text.

Select only the columns you need

SELECT * is convenient while exploring a table, but production queries should generally name the required columns. This makes the result set clearer and avoids returning unnecessary data:

SELECT id, name, city, state
FROM customer
WHERE state = 'CA';

Account for database-specific text behavior

Text comparisons can differ between database systems and configurations. Case sensitivity, collation, accents, whitespace, and normalization rules may affect whether a value matches. If an apparently matching comparison returns no rows, inspect the stored spelling and whitespace and check the database's collation and case-sensitivity behavior.

Troubleshooting AND and OR queries

More rows than expected

A mixed AND/OR query may be using the default precedence unintentionally. Add parentheses around the alternatives that belong together.

-- Intended: CA customers in Hope or Toronto
WHERE state = 'CA'
  AND (city = 'Hope' OR city = 'Toronto')

Missing-value query returns no rows

If the query uses address = NULL, replace it with address IS NULL. Use IS NOT NULL for present values.

Rows match only one requirement

If every requirement must match, use AND rather than OR. OR deliberately permits a row to qualify through only one condition.

String comparison causes a syntax error

Check that string literals use ordinary single quotes, such as state = 'CA', and that the column name is not accidentally enclosed as a value.

Optional practice table

You can create a compatible table for practice with this statement:

CREATE TABLE customer (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100),
  address VARCHAR(200),
  city VARCHAR(100),
  state VARCHAR(10),
  zip VARCHAR(20)
);

Summary

• AND combines conditions so every condition must be true.
• OR combines conditions so at least one condition must be true.
• AND usually narrows a result set; OR usually broadens it.
• AND returns the intersection of qualifying rows, while OR returns their union.
• AND has higher default precedence than OR.
• Use parentheses whenever mixed logic needs a specific grouping.
• Use IS NULL and IS NOT NULL rather than = NULL.
• Use single quotes for text literals and select specific columns when possible.

For a related reference, see SQL AND and OR operators.