WHERE: comparison and logic

Why WHERE?

Most questions are about some rows, not all: products under ₹500, orders not yet delivered, employees in Engineering. WHERE is the filter. The database checks the condition for each row and keeps only the rows where it is true.

Comparison operators

SELECT name, price FROM products WHERE price < 500;
-- Toor Dal 1kg     | 160
-- Steel Tiffin Box | 450

SQL uses a single = for comparing, not == as in Python or JavaScript. <> is the standard way to write "not equal"; != also works in SQLite, PostgreSQL, MySQL and SQL Server.

Text and dates are compared too. Text compares letter by letter and is case-sensitive, so 'pune' is not equal to 'Pune'. Dates stored as 'YYYY-MM-DD' text compare correctly, so hire_date >= '2020-01-01' means "hired in 2020 or later".

AND, OR and NOT

• AND: both conditions must be true. dept_id = 1 AND salary > 100000 gives Engineering staff earning above one lakh.
• OR: at least one condition must be true. stock < 20 OR price > 2000.
• NOT: flips a condition. NOT stock > 10 is the same as stock <= 10.

SELECT name, salary FROM employees WHERE dept_id = 1 AND salary > 100000;
-- Meera Iyer   | 250000
-- Rahul Sharma | 120000

Precedence: the classic bug

Precedence is the order in which parts of an expression are worked out. Just as multiplication comes before addition in maths, NOT comes first, then AND, then OR. That surprises people. The manager asks for "Kitchen or Fashion products under ₹1000", and someone writes:

SELECT name, category, price FROM products
WHERE category = 'Kitchen' OR category = 'Fashion' AND price < 1000;
-- Steel Tiffin Box   | Kitchen | 450
-- Pressure Cooker 3L | Kitchen | 1450   <- wrong! costs more than 1000
-- Cotton Kurta       | Fashion | 899

SELECT name, category, price FROM products
WHERE (category = 'Kitchen' OR category = 'Fashion') AND price < 1000;
-- Steel Tiffin Box | Kitchen | 450
-- Cotton Kurta     | Fashion | 899

NULL does not pass a comparison

Deepak Yadav has no department (dept_id is NULL). WHERE dept_id <> 1 does NOT return him, because comparing NULL with anything gives "unknown", and WHERE keeps only true. The next topic explains this and shows IS NULL.

Common mistakes

• Mixing AND and OR without brackets. Fix: bracket every OR group.
• Writing == instead of =. SQLite accepts ==, but most databases do not; use =.
• Quoting numbers or not quoting text: write price < 500 and city = 'Pune', not price < '500' or city = Pune.
• Comparing text with the wrong case. Store values in a consistent case, or compare with LOWER(), covered in module 3.
• Writing dates as '01-01-2020'. Compare in the stored YYYY-MM-DD form.