WHERE keeps only the rows for which a condition is true — rows where it is false or unknown (NULL) are dropped.

Operators

Kind	Operators	Example
Comparison	`= <> != < <= > >=`	`salary >= 100000`
Logical	`AND`, `OR`, `NOT`	`dept_id = 1 AND salary > 100000`
Range	`BETWEEN a AND b` (inclusive)	`hire_date BETWEEN '2018-01-01' AND '2020-12-31'`
List	`IN (…)`, `NOT IN (…)`	`dept_id IN (2, 3)`
Pattern	`LIKE`, `NOT LIKE`	`name LIKE 'A%'`
Null test	`IS NULL`, `IS NOT NULL`	`email IS NULL`

Comparison and logic

SQL

SELECT name, dept_id, salary
FROM employees
WHERE dept_id = 1 AND salary < 100000
ORDER BY emp_id;

Output

name  | dept_id | salary
------+---------+-------
Meera | 1       | 95000
Karan | 1       | 95000

SQL

SELECT name, hire_date
FROM employees
WHERE hire_date BETWEEN '2018-01-01' AND '2020-12-31'
ORDER BY hire_date;

Output

name   | hire_date
-------+-----------
Vikram | 2018-09-05
Meera  | 2019-01-10
Karan  | 2020-08-01

SQL

SELECT name, dept_id
FROM employees
WHERE dept_id IN (2, 3) AND NOT name = 'Sara'
ORDER BY emp_id;

Output

name   | dept_id
-------+--------
Priya  | 2
Arjun  | 2
Neha   | 2
Vikram | 3

Operator precedence: AND before OR

AND binds tighter than OR, exactly like × before +. Mixing them without parentheses is a classic bug.

SQL

-- intended: Sales or HR employees earning over 75000
SELECT name, dept_id, salary FROM employees
WHERE dept_id = 2 OR dept_id = 3 AND salary > 75000     -- means 2 OR (3 AND > 75000)
ORDER BY emp_id;

SELECT name, dept_id, salary FROM employees
WHERE (dept_id = 2 OR dept_id = 3) AND salary > 75000
ORDER BY emp_id;

Output

name   | dept_id | salary
-------+---------+-------
Priya  | 2       | 110000
Arjun  | 2       | 70000
Neha   | 2       | 72000
Vikram | 3       | 80000
Sara   | 3       | 85000

name   | dept_id | salary
-------+---------+-------
Priya  | 2       | 110000
Vikram | 3       | 80000
Sara   | 3       | 85000

BETWEEN with dates and times

SQL

SELECT order_id, order_date FROM orders
WHERE order_date >= '2024-02-01' AND order_date < '2024-03-01'
ORDER BY order_id;

Output

order_id | order_date
---------+-----------
103      | 2024-02-03
104      | 2024-02-14
105      | 2024-02-20

What is the difference between != and <>?

Is BETWEEN inclusive?