What you will be able to do
After this lesson you can build conditions with more than one test: people in Sales who earn a lot, staff in either of two departments, everyone except one group. You will also understand the one rule about AND and OR that causes more wrong results than any other, and how to avoid it.
AND: all conditions must hold
AND joins two conditions and keeps a row only when both are true:
SELECT first_name, department_id, salary
FROM employees
WHERE department_id = 1 AND salary >= 100000;+------------+---------------+--------+
| first_name | department_id | salary |
+------------+---------------+--------+
| Meera | 1 | 180000 |
| Arjun | 1 | 125000 |
| Kavya | 1 | 125000 |
+------------+---------------+--------+Three Engineering staff earn at least 100000. You can chain as many conditions as you need, and each extra AND narrows the result further.
OR: any condition may hold
OR keeps a row when at least one side is true, so it widens the result:
SELECT first_name, department_id, job_title
FROM employees
WHERE department_id = 4 OR department_id = 5;+------------+---------------+------------------+
| first_name | department_id | job_title |
+------------+---------------+------------------+
| Lata | 4 | Finance Manager |
| Omar | 4 | Accountant |
| Pooja | 5 | Support Lead |
| Karan | 5 | Support Engineer |
| Sana | 5 | Support Engineer |
+------------+---------------+------------------+A row that satisfies both sides is kept once, and it is not repeated. You can also test a column against several values with IN, which is shorter for this particular job and has its own lesson. OR is more general because the two conditions can be about different columns.
NOT: reversing a condition
NOT turns true into false and false into true:
SELECT first_name, job_title
FROM employees
WHERE NOT job_title = 'Developer' AND department_id = 1;+------------+----------------------+
| first_name | job_title |
+------------+----------------------+
| Meera | Engineering Director |
| Arjun | Senior Developer |
| Kavya | Senior Developer |
| Diya | Junior Developer |
+------------+----------------------+Often there is a direct operator that reads better: job_title <> 'Developer' means the same as NOT job_title = 'Developer'. NOT is most useful in front of a bracketed group or in front of IN, BETWEEN, LIKE and IS NULL, as the next lessons show.
The precedence trap
When one WHERE contains both AND and OR, the database groups the AND first, in the same way that multiplication happens before addition in arithmetic. Suppose you want people in Engineering or Sales who earn more than 100000. This query looks right and is wrong:
SELECT first_name, department_id, salary
FROM employees
WHERE department_id = 1 OR department_id = 2 AND salary > 100000;+------------+---------------+--------+
| first_name | department_id | salary |
+------------+---------------+--------+
| Meera | 1 | 180000 |
| Arjun | 1 | 125000 |
| Kavya | 1 | 125000 |
| Rohan | 1 | 82000 |
| Isha | 1 | 78000 |
| Vikram | 2 | 150000 |
| Diya | 1 | 60000 |
+------------+---------------+--------+Because of precedence, the database read it as: department 1 (anybody), or department 2 and a high salary. So low-paid Engineering staff such as Rohan, Isha and Diya came through. The fix is parentheses, which force the grouping you mean:
SELECT first_name, department_id, salary
FROM employees
WHERE (department_id = 1 OR department_id = 2) AND salary > 100000;+------------+---------------+--------+
| first_name | department_id | salary |
+------------+---------------+--------+
| Meera | 1 | 180000 |
| Arjun | 1 | 125000 |
| Kavya | 1 | 125000 |
| Vikram | 2 | 150000 |
+------------+---------------+--------+Now the OR is resolved first, and the salary test applies to both departments. Only four people appear: three from Engineering and Vikram from Sales, and every one of them earns more than 100000.
A habit worth keeping
Whenever a condition mixes AND and OR, add parentheses, even if the default grouping happens to be what you want. The parentheses cost nothing, they document your intent for the next reader, and they protect you if someone later edits the query. Parentheses can nest as deep as you like:
SELECT first_name, department_id, salary, hire_date
FROM employees
WHERE (department_id = 1 AND salary < 100000)
OR (department_id = 2 AND hire_date >= '2023-01-01');+------------+---------------+--------+------------+
| first_name | department_id | salary | hire_date |
+------------+---------------+--------+------------+
| Rohan | 1 | 82000 | 2021-06-14 |
| Isha | 1 | 78000 | 2022-09-05 |
| Tara | 2 | 52000 | 2023-04-03 |
| Diya | 1 | 60000 | 2024-06-10 |
| Aman | 2 | 50000 | 2024-03-18 |
+------------+---------------+--------+------------+NOT with a group
NOT followed by parentheses reverses the whole group. By De Morgan's rule, NOT (a OR b) is the same as NOT a AND NOT b:
SELECT first_name, department_id
FROM employees
WHERE NOT (department_id = 1 OR department_id = 2);+------------+---------------+
| first_name | department_id |
+------------+---------------+
| Dev | 3 |
| Anika | 3 |
| Yash | 3 |
| Lata | 4 |
| Omar | 4 |
| Pooja | 5 |
| Karan | 5 |
| Sana | 5 |
+------------+---------------+The result has eight rows, one for each employee in departments 3, 4 and 5. It does not include the consultant with no department, and the reason deserves its own lesson.
The NULL catch
A comparison with a missing value is not true and not false. It is unknown. AND, OR and NOT follow three-valued logic: NOT unknown is still unknown, and WHERE only keeps rows that are true. So Ravi Pillai, whose department_id is NULL, is missing from the result above, even though he is clearly not in departments 1 or 2. To include him, you must ask about the missing value explicitly, with IS NULL:
SELECT first_name, department_id
FROM employees
WHERE NOT (department_id = 1 OR department_id = 2) OR department_id IS NULL;+------------+---------------+
| first_name | department_id |
+------------+---------------+
| Dev | 3 |
| Anika | 3 |
| Yash | 3 |
| Lata | 4 |
| Omar | 4 |
| Pooja | 5 |
| Karan | 5 |
| Sana | 5 |
| Ravi | NULL |
+------------+---------------+The NULL lesson explains this in detail.
Differences between databases
AND, OR, NOT and parentheses behave identically on MySQL, PostgreSQL, SQLite and SQL Server. MySQL additionally accepts &&, || and ! as symbols for them, but those are not portable, and || means text joining on PostgreSQL and SQLite. Stick to the words. The matching MySQL page is AND, OR, NOT, IN and BETWEEN.
Common mistakes
- Mixing
ANDandORwithout parentheses, as in the precedence trap above. - Writing
WHERE department_id = 1 OR 2. This does not mean "1 or 2". Repeat the column:department_id = 1 OR department_id = 2, or useIN (1, 2). - Using
ANDwhen you meanOR. One column cannot equal two different values at the same time, sodepartment_id = 1 AND department_id = 2returns nothing. - Forgetting the NULL rows when you negate a condition.
Exercise
Find employees who work in Support (department 5) or earn at least 110000, but who were hired before 2020-01-01. Show first_name, department_id, salary and hire_date.
Show one possible answer
SELECT first_name, department_id, salary, hire_date
FROM employees
WHERE (department_id = 5 OR salary >= 110000)
AND hire_date < '2020-01-01';+------------+---------------+--------+------------+
| first_name | department_id | salary | hire_date |
+------------+---------------+--------+------------+
| Meera | 1 | 180000 | 2018-03-12 |
| Arjun | 1 | 125000 | 2019-07-01 |
| Vikram | 2 | 150000 | 2017-11-02 |
| Dev | 3 | 110000 | 2018-10-15 |
| Lata | 4 | 115000 | 2016-04-25 |
| Pooja | 5 | 85000 | 2019-09-23 |
+------------+---------------+--------+------------+The parentheses make the date condition apply to both alternatives. Without them, every high earner would be listed regardless of hire date.
Key points
- AND keeps a row only if both conditions are true. OR keeps it if at least one is true.
- NOT reverses a condition. NOT (a = b) is the same idea as a <> b.
- AND is evaluated before OR, so mixing them without parentheses often gives a surprising result.
- Add parentheses whenever you mix AND and OR, even where they are not strictly needed.
- Rows where a condition is unknown because of NULL are not kept by AND, OR or NOT.