SQL WHERE Clause and Operators

A table with a million rows is rarely useful as a whole. WHERE lets you keep only the rows that meet a condition, dropping everything else from the result.

For this lesson, imagine an employees table with these rows.

Comparing Values and Combining Conditions

The basic comparison operators are =, <> (not equal), >, <, >= and <=. You can join several conditions with AND, OR and NOT.

AND needs both sides to be true. OR needs only one. When you mix them, AND is evaluated first, so use brackets to make your intention clear.

SELECT name, department, salary
FROM employees
WHERE department = 'IT' OR salary > 60000;

Output:

Code Explanation:

• The database checks each row against the condition one at a time.
• Sneha and Kabir pass because they are in IT. Meera and Isha pass because their salary is above 60000.
• Aarav and Rohan match neither side, so they are left out.

Shortcuts for Common Conditions

Three operators save you from writing long chains of OR and AND.

• IN checks a value against a list. city IN ('Pune', 'Delhi') replaces city = 'Pune' OR city = 'Delhi'.
• BETWEEN checks a range, including both ends. salary BETWEEN 45000 AND 60000 means 45000 or more and 60000 or less.
• LIKE matches text patterns. The % sign stands for any number of characters, and _ stands for exactly one.

SELECT name, city, salary
FROM employees
WHERE city IN ('Pune', 'Delhi')
  AND salary BETWEEN 45000 AND 60000;

Output:

Code Explanation:

• The IN filter keeps only Pune and Delhi, which removes Meera and Kabir.
• Sneha is in Pune but earns 75000, so BETWEEN removes her.
• Isha is in Delhi but earns 61000, just over the limit.
• Rohan stays because 45000 is the lower edge of the range, and the edge counts.

A pattern like name LIKE 'S%' would return Sneha, and name LIKE '%a' would return Meera, Sneha and Isha. In PostgreSQL, LIKE is case-sensitive, so use ILIKE when you want to ignore capital letters. MySQL ignores case by default.