AND, OR, NOT

Sometimes a single WHERE condition isn’t enough. You might need to find products that are both in the ‘Electronics’ category and cost less than $100. SQL provides logical operators—AND, OR, and NOT—to let you combine multiple conditions.

The AND Operator

AND requires both conditions to be true for a row to be included in the results.

SELECT * FROM products 
WHERE category = 'Home' AND price < 100;

This query only returns Home products that are cheap. A $200 Home product won’t show up. A $50 Electronics product won’t show up.

The OR Operator

OR requires at least one condition to be true.

SELECT * FROM products 
WHERE category = 'Home' OR category = 'Office';

This is useful when you want data that matches any item from a list of possibilities. (Note: A more advanced way to write this is WHERE category IN ('Home', 'Office')).

The NOT Operator

NOT reverses a condition. It includes rows where the condition is false.

SELECT * FROM products 
WHERE NOT category = 'Electronics';

This gives you everything except Electronics. (You could also write this as WHERE category != 'Electronics').

Controlling Logic with Parentheses

When you mix AND and OR in the same query, SQL evaluates AND before OR (just like multiplication happens before addition in math).

If you want to find products that are either ‘Home’ or ‘Office’ gear, but they must have a price under $100, you need to use parentheses () to group your logic:

SELECT * FROM products 
WHERE (category = 'Home' OR category = 'Office') 
AND price < 100;

Without the parentheses, category = 'Office' AND price < 100 would be evaluated together as a single block, radically changing the results!

[!TIP] Whenever you mix AND and OR in a single WHERE clause, always use parentheses. It prevents logical errors and makes your code much easier for other humans to read.

Try using the AND operator in the lab to help the sales team find expensive electronics!