Using Multiple Condition Filters

Combine conditions in a WHERE clause with AND, OR and NOT, and use parentheses so they're checked the way you mean.

Apprentice · T-SQL · SQL · Updated

After this lesson you can

  • Combine filters with AND, OR, NOT and parentheses

Key takeaways

  • AND keeps a row only if every condition is true; OR keeps it if at least one is.
  • NOT turns a condition around, and reads naturally with the other filters: NOT LIKE, NOT IN, NOT BETWEEN and IS NOT NULL.
  • AND is checked before OR, so mixing them without parentheses can return rows you didn't mean.
  • Whenever AND and OR appear in the same WHERE, add parentheses to make your meaning clear.

A real question often has more than one part. Expensive products we have plenty of. Customers in London or Madrid. SQL joins conditions with AND, OR and NOT, just as C# uses &&, || and !.

AND: every condition

With AND, a row is kept only if all its conditions are true:

SELECT ProductName, UnitPrice, UnitsInStock
FROM Products
WHERE UnitPrice > 40 AND UnitsInStock > 20;
ProductName UnitPrice UnitsInStock
Mishi Kobe Niku 97.00 29
Carnarvon Tigers 62.50 42
Sir Rodney's Marmalade 81.00 40
Schoggi Schokolade 43.90 49
Rössle Sauerkraut 45.60 26
Raclette Courdavault 55.00 79
Vegie-spread 43.90 24

OR: at least one condition

With OR, a row is kept if any of its conditions is true:

SELECT CompanyName, City
FROM Customers
WHERE City = 'London' OR City = 'Madrid';

That returns 9 customers: 6 in London and 3 in Madrid. (City IN ('London', 'Madrid') says the same thing.)

NOT: the opposite

NOT turns a condition around:

SELECT ProductName, UnitPrice
FROM Products
WHERE NOT CategoryId = 1;

It also reads naturally with the other filters: NOT LIKE, NOT IN, NOT BETWEEN and IS NOT NULL.

AND and OR together: use parentheses

When a WHERE mixes AND and OR, AND is checked first, just as * comes before +. That can give a different answer from the one you meant. Suppose you want cheap drinks or sauces: products in category 1 (Beverages) or 2 (Condiments) that cost under $10:

SELECT ProductName, UnitPrice
FROM Products
WHERE (CategoryId = 1 OR CategoryId = 2) AND UnitPrice < 10;
ProductName UnitPrice
Guaraná Fantástica 4.50
Rhönbräu Klosterbier 7.75

Without the parentheses, CategoryId = 1 OR CategoryId = 2 AND UnitPrice < 10 means "every beverage, whatever its price, or a condiment under $10", and returns all 12 beverages.

When AND and OR appear in the same WHERE, always add parentheses, even when you think the order is already right. They make your meaning clear to the database and to the next person who reads the query.

Check yourself

2 questions to try on the lesson page.

So what

AND narrows, OR widens, and parentheses make sure the database reads your question exactly the way you asked it.

Key terms

Cheat sheets