SQL AND, OR, NOT Operators Explained for Beginners
Learn SQL AND, OR and NOT operators with simple examples. See how to combine conditions in WHERE and avoid the common mistakes beginners make.
Learn SQL AND, OR and NOT operators with simple examples. See how to combine conditions in WHERE and avoid the common mistakes beginners make.
What are the SQL AND, OR and NOT operators? They are small words that let you combine several conditions in one WHERE clause. AND means every condition must be true. OR means at least one condition must be true. NOT flips a condition so that true becomes false. With them, you can ask a database real questions like "show me customers from Canada who spent more than 100 dollars".
This guide is for absolute beginners. We explain every term the first time we use it, and every example is small enough to copy and run. If you have not yet learned how a basic filter works, read our SELECT and WHERE guide first, then come back here.
SQL (Structured Query Language) is the language used to talk to databases. A database stores data in tables, which look like spreadsheets: rows go across, columns go down. The WHERE clause keeps only the rows that match a condition. A condition is a yes-or-no test, such as country = 'Canada'.
One condition is often not enough. That is where AND, OR and NOT come in. They are called logical operators because they work with true and false.
Imagine a table called customers with a few rows. You can create it in any SQL database, such as PostgreSQL, MySQL or SQLite.
CREATE TABLE customers (
id INT,
name TEXT,
country TEXT,
total_spent INT
);
INSERT INTO customers (id, name, country, total_spent) VALUES
(1, 'Ana', 'Canada', 150),
(2, 'Ben', 'Canada', 40),
(3, 'Chen', 'China', 220),
(4, 'Dara', 'Kenya', 90),
(5, 'Eli', 'Brazil', 300);The first statement builds the table with four columns. The second adds five rows. Each name in parentheses after INSERT INTO is a column, and each line after VALUES is one row. Text goes inside single quotes; numbers do not.
Use AND when you want rows that pass two or more tests at the same time.
SELECT name, country, total_spent
FROM customers
WHERE country = 'Canada'
AND total_spent > 100;Line 1 picks the columns to show. Line 2 names the table. Line 3 is the first test: the country must be Canada. Line 4 adds a second test: total_spent must be more than 100. Only Ana passes both, so only Ana is returned. Ben is in Canada but spent only 40, so he is dropped.
Use OR when a row only needs to pass one of the tests.
SELECT name, country
FROM customers
WHERE country = 'Canada'
OR country = 'Kenya';This returns Ana, Ben and Dara. A row is kept if the country is Canada, or Kenya, or both. Here a row cannot be both, but in other queries it can. Notice that you must write the column name again after OR. Writing country = 'Canada' OR 'Kenya' is a common mistake and will not work.
NOT turns a true test into false, and a false test into true. It is useful for saying "everything except".
SELECT name, country
FROM customers
WHERE NOT country = 'Canada';This returns everyone who is not from Canada: Chen, Dara and Eli. You can get the same result with the not-equal operator, country <> 'Canada'. NOT is most useful together with other keywords such as NOT IN, NOT LIKE and NOT BETWEEN.
SELECT name
FROM customers
WHERE country NOT IN ('Canada', 'China');The IN list holds several values. NOT IN keeps rows whose country is not in that list, so you get Dara and Eli.
| Operator | Rule | Example | Result |
|---|---|---|---|
| AND | All conditions must be true | country = 'Canada' AND total_spent > 100 | Ana |
| OR | At least one condition is true | country = 'Canada' OR country = 'Kenya' | Ana, Ben, Dara |
| NOT | Reverses the condition | NOT country = 'Canada' | Chen, Dara, Eli |
Things get tricky when you mix AND with OR. SQL checks AND before OR, just like multiplication comes before addition in maths. This is called operator precedence. Look at this query.
SELECT name, country, total_spent
FROM customers
WHERE country = 'Canada'
OR country = 'China'
AND total_spent > 200;You might think this means "Canada or China, with more than 200 spent". But SQL reads it as "Canada, or (China and more than 200)". So Ana and Ben (both in Canada, any spend) are returned, plus Chen. Ben appears even though he spent only 40, which is probably not what you wanted.
Parentheses group conditions together. SQL works out what is inside them first. Always use them when you mix AND and OR.
SELECT name, country, total_spent
FROM customers
WHERE (country = 'Canada' OR country = 'China')
AND total_spent > 100;Now the OR part is grouped. A row must be in Canada or China, and also have total_spent above 100. Ana and Chen pass. Ben does not. Even when parentheses are not required, they make your query easier for humans to read.
Try each one yourself before looking for answers. Run the query, read the rows it returns, and ask whether they match what you expected. That habit is the fastest way to learn SQL.
Want to filter text more flexibly? Learn how the LIKE operator and wildcards work next.
Read the SQL LIKE guideAND keeps a row only when every condition is true. OR keeps a row when at least one condition is true. AND makes results smaller, while OR usually makes them larger.
AND runs before OR. To control the order, wrap the parts you want evaluated first in parentheses. This avoids surprising results.
Yes. Just use parentheses to group them, for example WHERE (a = 1 OR b = 2) AND c = 3. This makes your meaning clear to both the database and other people.
Put NOT before a condition to reverse it, such as WHERE NOT country = 'Canada'. You can also use it with keywords like NOT IN, NOT LIKE, NOT BETWEEN and IS NOT NULL.
OR keeps a row if any single condition matches, so it can let in more rows than expected. If you also use AND, check that parentheses group the conditions the way you intended.
Need help building a database-driven website or app? Talk to the Fepiq team.
Contact FepiqOccasional, no-fluff notes on shipping modern software — startups, automation, Laravel, Shopify and more. No spam, unsubscribe anytime.
Keep reading
Learn JavaScript template literals with simple examples: backticks, ${} variables, and multi-line strings. A beginner-friendly guide you can copy and run.
PostgreSQL date and time for beginners: learn DATE, TIMESTAMP, NOW(), INTERVAL and filtering by date with simple, copy-paste examples you can run today.
Let's build something
Book a free discovery call. We'll listen, ask sharp questions, and send you a proposal within 3 business days.