Back to all posts

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
  • condition is a test that is true or false for each row, such as age = 20.
  • AND keeps a row when the conditions on both sides are true.
  • OR keeps a row when at least one side is true.
  • NOT reverses 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.

Three diagrams of the five students in two overlapping circles, age = 20 and grade = 'A': AND keeps the overlap, Alice; OR keeps both circles, Alice, Carol and David; NOT grade = 'A' keeps everything outside the grade circle, Bob, David and Eve

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;
nameagegrade
Alice20A

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;
nameagegrade
Alice20A
Carol21A
David20C

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;
nameagegrade
Bob22B
David20C
Eve23B

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;
nameagegrade
Alice20A
Carol21A

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;
nameagegrade
Alice20A

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;
nameagegrade
David20C

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;
nameagegrade
Bob22B
Carol21A
David20C
Eve23B

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:

nameagegrade
Bob22B
David20C
Eve23B

The PostgreSQL 18 and MySQL 8.4 manuals give these results for each grade:

gradegrade = 'A'NOT grade = 'A'Row kept by NOT
Atruefalseno
Bfalsetrueyes
NULLNULLNULLno

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;
nameagegrade
Bob22B
David20C
Eve23B
Frank21NULL

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.

The parentheses query in the DbSchema SQL Editor, with its one row, Alice, in the result grid under the editor

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.