Operators
A support agent wants "pending orders, plus any shipped order with more than five items". She types a condition, runs it, and gets every shipped order in the table. Nothing is broken; she just combined AND and OR without knowing which one wins. Operators are small, but their rules decide which rows you get.
The rule
Compare, then combine with AND/OR — and test NULL only with IS.
Comparison operators produce true or false for each row: =, <> (not equal; most databases also accept !=), <, >, <=, >=.
Arithmetic operators compute values: +, -, *, /, and % for remainder in many databases. You can use them in the output as well as in conditions — SELECT name, price * 1.18 AS price_with_tax FROM products; shows each price with 18% tax added. One surprise: in PostgreSQL, SQL Server and SQLite, dividing one integer by another throws away the fraction, so 7 / 2 gives 3. Write 7.0 / 2 when you want 3.5.
Logical operators combine conditions: AND (both true), OR (at least one true), NOT (flip it).
Special operators save typing:
SELECT * FROM products WHERE price BETWEEN 100 AND 500; -- inclusive at both ends
SELECT * FROM customers WHERE city IN ('Pune', 'Delhi'); -- any value in the list
SELECT * FROM customers WHERE name LIKE 'A%'; -- % = any run of characters
SELECT * FROM customers WHERE name LIKE '_a%'; -- _ = exactly one character
SELECT * FROM customers WHERE email IS NULL; -- the only way to test NULLNULL means "unknown", so email = NULL is itself unknown for every row, and WHERE keeps only rows where the condition is true. That query returns nothing. Always write IS NULL or IS NOT NULL.
The shortcut
AND binds tighter than OR, just as multiplication binds tighter than addition. Read A OR B AND C as A OR (B AND C). When you mean something else, add parentheses — and add them anyway when in doubt.
What she wrote
WHERE status = 'shipped' OR status = 'pending' AND quantity > 5
Drills
Answer fast — trust the rule.
Does price BETWEEN 100 AND 500 include a product priced exactly 500?
Why does SELECT * FROM customers WHERE email = NULL; return no rows even when some emails are missing?
Which name matches name LIKE '_a%'?
How does SQL evaluate city = 'Pune' OR city = 'Delhi' AND joined_on > '2025-01-01'?
Which condition finds products whose category is neither 'books' nor 'toys'?
Remember
- Compare, then combine with AND/OR — and test NULL only with IS.
- AND binds tighter than OR; parentheses make intent explicit.
- BETWEEN includes both ends;
%is any run of characters,_is exactly one.