SQL AND, OR, NOT Operators Explained with Examples
For SQL beginners who can filter rows with one condition in WHERE and need to combine two or more.
On this page
AND, OR and NOT combine conditions in a WHERE clause: AND needs every condition true, OR needs one true condition, and NOT reverses a condition. For example, AND finds the orders that are both unpaid and overdue.
Every combination is built from these forms:
WHERE condition1 AND condition2
WHERE condition1 OR condition2
WHERE NOT condition
WHERE (condition1 OR condition2) AND condition3
conditionis a test that is true or false for each row, such asage = 20.ANDkeeps a row when the conditions on both sides are true.ORkeeps a row when at least one side is true.NOTreverses the condition after it, and keeps a row when that condition is false.- Parentheses group conditions. The database checks the conditions inside the parentheses first.
Create a sample table of five students:
CREATE TABLE students (
name text,
age int,
grade text
);
INSERT INTO students VALUES
('Alice', 20, 'A'),
('Bob', 22, 'B'),
('Carol', 21, 'A'),
('David', 20, 'C'),
('Eve', 23, 'B');
In each panel of the diagram, one circle holds the students with age = 20, and the other circle holds those with grade = 'A'. The shaded area marks the rows that the operator returns.
Every query result below comes from PostgreSQL 18.6 and SQLite 3.50. Both returned the same rows. ORDER BY name in each query only sorts the rows.
AND
AND keeps a row only when every condition is true. For example, you can find the students who are 20 and have an A.
SELECT * FROM students
WHERE age = 20 AND grade = 'A'
ORDER BY name;
| name | age | grade |
|---|---|---|
| Alice | 20 | A |
Alice is the only student who passes both tests. Carol has an A but is 21, and David is 20 but has a C. AND drops both.
OR
OR keeps a row when at least one condition is true. For example, you can find the students who are 20 or have an A.
SELECT * FROM students
WHERE age = 20 OR grade = 'A'
ORDER BY name;
| name | age | grade |
|---|---|---|
| Alice | 20 | A |
| Carol | 21 | A |
| David | 20 | C |
Alice passes both tests. David passes the age test, and Carol passes the grade test. Bob and Eve pass neither test, so OR drops them.
NOT
NOT reverses the condition after it: true becomes false, and false becomes true. For example, you can find every student who does not have an A.
SELECT * FROM students
WHERE NOT grade = 'A'
ORDER BY name;
| name | age | grade |
|---|---|---|
| Bob | 22 | B |
| David | 20 | C |
| Eve | 23 | B |
Alice and Carol have an A, so NOT makes their test false and drops their rows. Bob, David and Eve come back. The not-equal test grade <> 'A' returns the same three rows.
Parentheses and precedence
When a WHERE clause mixes AND and OR, SQL applies AND first. This order is called precedence, and parentheses change it. The query below tries to find the students who are 20 and have an A or a B:
SELECT * FROM students
WHERE grade = 'A' OR grade = 'B' AND age = 20
ORDER BY name;
| name | age | grade |
|---|---|---|
| Alice | 20 | A |
| Carol | 21 | A |
Carol is 21, but she is in the result. SQL applied AND first and read the query as grade = 'A' OR (grade = 'B' AND age = 20). Alice and Carol pass grade = 'A', and that one test is enough for OR.
Put the OR in parentheses. The database then applies the OR first:
SELECT * FROM students
WHERE (grade = 'A' OR grade = 'B') AND age = 20
ORDER BY name;
| name | age | grade |
|---|---|---|
| Alice | 20 | A |
Only Alice is 20 and has an A or a B. Now every student has to be 20. Without the parentheses, only the B students had to be 20.
SQL applies comparisons such as = and < first, then NOT, then AND, then OR. This order is the same in the manuals of PostgreSQL 18, MySQL 8.4, SQLite, SQL Server and Oracle.
Whenever AND and OR share a WHERE clause, add parentheses, even when the result is already right. Anyone who reads the query then sees the order at a glance.
Practice this in Lesson 3 of the SQL tutorial: you run WHERE, AND, OR, IN, BETWEEN and LIKE on a shop database in your browser, and each exercise is checked.
What goes wrong
NOT covers one condition
SQL applies NOT before AND, so NOT reverses only the condition right after it. The query below seems to exclude the students who are 20 and have an A:
SELECT * FROM students
WHERE NOT grade = 'A' AND age = 20
ORDER BY name;
| name | age | grade |
|---|---|---|
| David | 20 | C |
NOT reversed only grade = 'A', so the query kept the students who are 20 and do not have an A. To reverse both tests together, put them in parentheses:
SELECT * FROM students
WHERE NOT (grade = 'A' AND age = 20)
ORDER BY name;
| name | age | grade |
|---|---|---|
| Bob | 22 | B |
| Carol | 21 | A |
| David | 20 | C |
| Eve | 23 | B |
Alice is the only student who is 20 and has an A, so only Alice is missing.
NOT and NULL
NULL marks a missing value. A comparison with NULL is neither true nor false: its result is unknown, which SQL also writes as NULL. NOT reverses true and false, but unknown stays unknown.
WHERE keeps only the rows where the condition is true. Unknown is not true, so NOT drops every row with NULL in the column that it tests.
Add a sixth student with no grade, and run the NOT query again:
INSERT INTO students VALUES ('Frank', 21, NULL);
SELECT * FROM students
WHERE NOT grade = 'A'
ORDER BY name;
Frank does not have an A, yet the result leaves him out:
| name | age | grade |
|---|---|---|
| Bob | 22 | B |
| David | 20 | C |
| Eve | 23 | B |
The PostgreSQL 18 and MySQL 8.4 manuals give these results for each grade:
| grade | grade = 'A' | NOT grade = 'A' | Row kept by NOT |
|---|---|---|---|
| A | true | false | no |
| B | false | true | yes |
| NULL | NULL | NULL | no |
To keep the rows with NULL, add OR grade IS NULL. IS NULL is true when the value is NULL and false otherwise, never unknown:
SELECT * FROM students
WHERE NOT grade = 'A' OR grade IS NULL
ORDER BY name;
| name | age | grade |
|---|---|---|
| Bob | 22 | B |
| David | 20 | C |
| Eve | 23 | B |
| Frank | 21 | NULL |
Frank now comes back with Bob, David and Eve. Add OR grade IS NULL to grade <> 'A' as well. grade <> 'A' alone drops Frank in the same way.
The tutorial on NULL values explains IS NULL and COALESCE.
Run the queries in DbSchema
The SQL Editor in DbSchema runs a query and shows its rows in a grid under the editor. Press Editor in the toolbar, choose SQL Editor, paste a query from this article, and press Run.
To check a long filter, run its first condition alone. Then add one condition at a time, and compare the rows with the previous run.
Download DbSchema at https://dbschema.com/download.html and connect it to PostgreSQL, MySQL, or SQLite. Paste the CREATE TABLE and INSERT statements into the SQL Editor, then run each query and change its conditions. The free Community Edition includes the database connection and the SQL Editor.
Test your filters against a real schema
DbSchema reverse-engineers your database, runs your SQL in a built-in editor and reports the rows each statement returned. The free Community Edition covers all of it.