logical operators (AND, OR, NOT)

One condition is rarely enough. Like "orders from Delhi" is fine but "delivered orders from Delhi above 1000" is what people actually ask for. That's where AND, OR and NOT come in.

AND

Both sides have to be true.

SELECT * FROM products
WHERE category = 'Electronics' AND price < 1000;

Gets you the mouse and the cable, the keyboard is Electronics but too expensive so it's out.

OR

At least one side has to be true.

SELECT * FROM customers
WHERE city = 'Delhi' OR city = 'Chennai';

NOT

Flips whatever comes after it.

SELECT * FROM orders WHERE NOT status = 'delivered';
SELECT * FROM customers WHERE city NOT IN ('Delhi', 'Mumbai');
SELECT * FROM products WHERE product_name NOT LIKE '%Pen%';

the part everyone gets wrong: AND runs before OR

See this query, looks fine right?

-- "electronics or stationery, but only cheap stuff"
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Stationery' AND price < 200;

It's not. Sql reads it like this:

WHERE category = 'Electronics' OR (category = 'Stationery' AND price < 200)

So you get every electronics item including the 10999 monitor. AND sticks together first, same way multiplication happens before addition in math.

Fix is just brackets:

SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Stationery') AND price < 200;

Honestly whenever you mix AND and OR, just put brackets. Even if it's technically not needed, the next person reading it (probably you in 2 months) will thank you.

a quick way to think about it

A	B	A AND B	A OR B
true	true	true	true
true	false	false	true
false	false	false	false

And then NULL makes it weirder, true AND NULL is NULL, true OR NULL is true. Next topic explains why.

stuff that trips people up

• mixing AND and OR without brackets, by far the #1 thing.
• WHERE city = 'Delhi' OR 'Mumbai', doesn't work, you have to repeat the column, city = 'Delhi' OR city = 'Mumbai' or just use IN.
• NOT IN with a list that has a NULL in it returns nothing. Yeah really. Covered later.

practice

Check queries.sql.

-- logical operators
-- run sample-data.sql first

-- examples ---------------------------------------------------------

SELECT * FROM products WHERE category = 'Electronics' AND price < 1000;

SELECT * FROM customers WHERE city = 'Delhi' OR city = 'Chennai';

SELECT * FROM orders WHERE NOT status = 'delivered';

-- wrong, AND runs first so you get all electronics
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Stationery' AND price < 200;

-- right
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Stationery') AND price < 200;


-- practice ---------------------------------------------------------

-- 1. delivered orders with amount above 1000
-- 2. employees in Engineering earning less than 100000
-- 3. customers from Delhi or Mumbai who have a phone number
-- 4. products that are NOT Electronics and cost more than 200
-- 5. orders that are pending or shipped, placed after 2024-04-01
-- 6. employees who are either in Sales, or earn more than 140000


-- answers ----------------------------------------------------------

-- 1
SELECT * FROM orders WHERE status = 'delivered' AND amount > 1000;

-- 2
SELECT * FROM employees WHERE department = 'Engineering' AND salary < 100000;

-- 3
SELECT * FROM customers
WHERE (city = 'Delhi' OR city = 'Mumbai') AND phone IS NOT NULL;

-- 4
SELECT * FROM products WHERE NOT category = 'Electronics' AND price > 200;

-- 5
SELECT * FROM orders
WHERE (status = 'pending' OR status = 'shipped') AND order_date > '2024-04-01';

-- 6
SELECT * FROM employees WHERE department = 'Sales' OR salary > 140000;