SQL - WHERE & Operators
Overview
Filter rows using predicates. Combine with AND/OR/NOT. Beware NULL semantics: comparisons with NULL are UNKNOWN—use IS NULL.
Common predicates
- Comparison: =, !=/<> (not equal), <, <=, >, >=
- Ranges: BETWEEN
- Sets: IN, NOT IN
- Patterns: LIKE with % and _
- Null checks: IS NULL / IS NOT NULL
SELECT *
FROM employees
WHERE salary BETWEEN 80000 AND 150000
AND first_name LIKE 'A%'
AND department_id IN (1,2,3)
AND hired_at IS NOT NULL;
SELECT *
FROM employees
WHERE salary BETWEEN 80000 AND 150000
AND first_name LIKE N'A%'
AND department_id IN (1,2,3)
AND hired_at IS NOT NULL;
SELECT *
FROM employees
WHERE salary BETWEEN 80000 AND 150000
AND first_name LIKE 'A%'
AND department_id IN (1,2,3)
AND hired_at IS NOT NULL;
Prerequisite
Load the Standard Test Data.
Expected Output
Given the test dataset, this filter returns only Ada:
| id | first_name | department_id | salary | hired_at |
|---|---|---|---|---|
| 1 | Ada | 1 | 120000 | 2021-01-10 10:00:00 |
Sample Use Cases (5)
1) Filter by gender = 'Female'
SELECT id, first_name, gender
FROM employees
WHERE gender = 'Female';
SELECT id, first_name, gender
FROM employees
WHERE gender = N'Female';
SELECT id, first_name, gender
FROM employees
WHERE gender = 'Female';
| id | first_name | gender |
|---|---|---|
| 1 | Ada | Female |
| 4 | Dee | Female |
2) Salary >= 100000
SELECT id, first_name, salary
FROM employees
WHERE salary >= 100000;
SELECT id, first_name, salary
FROM employees
WHERE salary >= 100000;
SELECT id, first_name, salary
FROM employees
WHERE salary >= 100000;
| id | first_name | salary |
|---|---|---|
| 5 | Eli | 130000 |
| 1 | Ada | 120000 |
| 4 | Dee | 110000 |
3) Names starting with B or E
SELECT id, first_name
FROM employees
WHERE first_name LIKE 'B%'
OR first_name LIKE 'E%';
SELECT id, first_name
FROM employees
WHERE first_name LIKE N'B%'
OR first_name LIKE N'E%';
SELECT id, first_name
FROM employees
WHERE first_name LIKE 'B%'
OR first_name LIKE 'E%';
| id | first_name |
|---|---|
| 2 | Bob |
| 5 | Eli |
4) Employees in the 'Engineering' department
SELECT e.id, e.first_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';
SELECT e.id, e.first_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = N'Engineering';
SELECT e.id, e.first_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';
| id | first_name | department |
|---|---|---|
| 1 | Ada | Engineering |
| 2 | Bob | Engineering |
| 5 | Eli | Engineering |
5) Hired during year 2021
SELECT id, first_name, hired_at
FROM employees
WHERE hired_at >= '2021-01-01' AND hired_at < '2022-01-01';
SELECT id, first_name, hired_at
FROM employees
WHERE hired_at >= '2021-01-01' AND hired_at < '2022-01-01';
SELECT id, first_name, hired_at
FROM employees
WHERE hired_at >= '2021-01-01' AND hired_at < '2022-01-01';
| id | first_name | hired_at |
|---|---|---|
| 1 | Ada | 2021-01-10 10:00:00 |
| 2 | Bob | 2021-05-05 09:30:00 |