SQL AND, OR, NOT: Combine Conditions Correctly

Real questions usually have more than one condition: engineers earning over 80,000, employees in Mumbai or Delhi, everyone except Sales. AND, OR and NOT let you combine conditions in a WHERE clause.

The example table#

AND: every condition must be true#

SELECT name, department, salary
FROM employees
WHERE department = 'Engineering' AND salary > 80000;

name  | department  | salary
------+-------------+-------
Meera | Engineering | 81000

Ravi is in Engineering but earns 78,000, so the second condition fails and he is left out. Each AND makes the result smaller or equal.

OR: at least one condition must be true#

SELECT name, city
FROM employees
WHERE city = 'Mumbai' OR city = 'Delhi';

name  | city
------+-------
Asha  | Mumbai
John  | Mumbai
Priya | Delhi

Each OR makes the result larger or equal.

NOT: reverse a condition#

SELECT name, department
FROM employees
WHERE NOT department = 'Sales';

name  | department
------+------------
Ravi  | Engineering
Meera | Engineering
John  | Marketing

NOT department = 'Sales' means the same as department <> 'Sales'. NOT is most useful with the other operators:

Truth tables#

AND runs before OR#

This is the most important rule in this lesson. Just as multiplication happens before addition in maths, AND is evaluated before OR. Look at this query:

SELECT name, department, city
FROM employees
WHERE department = 'Sales' OR department = 'Marketing' AND city = 'Mumbai';

name  | department | city
------+------------+-------
Asha  | Sales      | Mumbai
John  | Marketing  | Mumbai
Priya | Sales      | Delhi

You might have meant "Sales or Marketing people in Mumbai", but Priya from Delhi is included. SQL read it as:

WHERE department = 'Sales'
   OR (department = 'Marketing' AND city = 'Mumbai')

Add parentheses to say what you mean:

SELECT name, department, city
FROM employees
WHERE (department = 'Sales' OR department = 'Marketing')
  AND city = 'Mumbai';

name | department | city
-----+------------+-------
Asha | Sales      | Mumbai
John | Marketing  | Mumbai

The full order of evaluation is: comparisons (=, >, …) first, then NOT, then AND, then OR.

Combining everything#

Employees outside Mumbai and Pune who earn at least 60,000:

SELECT name, city, salary
FROM employees
WHERE city NOT IN ('Mumbai', 'Pune')
  AND salary >= 60000;

name  | city      | salary
------+-----------+-------
Ravi  | Bengaluru | 78000
Priya | Delhi     | 61000

A note on NULL#

Conditions involving NULL are neither true nor false; they are unknown, and WHERE keeps only rows where the condition is true. That means NOT does not always return "everything else". This is explained in detail in NULL Values.

Common mistakes#

Practice#

SELECT name, department, salary
FROM employees
WHERE department <> 'Marketing'
  AND (salary < 55000 OR salary > 80000);

name  | department  | salary
------+-------------+-------
Asha  | Sales       | 52000
Meera | Engineering | 81000

Key takeaway#