SQL AND, OR, NOT Operators for Combining Conditions in SQL

In the previous guide, you learned how to filter rows using the WHERE clause with a single condition. But real-world questions are rarely that simple. You do not just want "products over $50." You want "products over $50 that are currently in stock." Or "customers from New York or Chicago who signed up this year." Or "all orders that are not pending."

This is where logical operators come in. SQL provides three logical operators, AND, OR, and NOT, that let you combine multiple conditions into a single WHERE clause. These operators unlock the ability to ask complex, precise questions about your data in a single query.

This guide covers each logical operator in depth, explains the critical concept of operator precedence (and why it causes bugs), shows you how to use parentheses to write clear and correct conditions, and walks you through real-world multi-condition filters. Every example uses the ShopSmart sample database with full outputs so you can follow along (that we defined in a previous guide here).

The AND Operator

AND combines two or more conditions and requires all of them to be true for a row to appear in the results. If even one condition is false, the entire expression is false and the row is excluded.

SELECT columns
FROM table
WHERE condition1 AND condition2;

Think of AND as a strict doorman at a club who checks every item on the list. "Do you meet requirement one? Yes? And requirement two? Yes? And requirement three? Also yes? Alright, you can come in." Fail any single check, and you are turned away.

Basic AND Examples

Find products that cost more than $40 and are currently available:

SELECT name, price, is_available
FROM products
WHERE price > 40 AND is_available = true;

Output:

name	price	is_available
Mechanical Keyboard	89.99	true
USB-C Hub	45.00	true
Data Science Handbook	42.50	true
Coffee Maker Pro	129.99	true
Running Shoes X1	110.00	true

The Bluetooth Speaker costs $65.00 (passes the price condition) but has is_available = false (fails the second condition), so it is excluded. Both conditions must be true.

Find customers from New York who signed up in 2023:

SELECT first_name, last_name, city, signup_date
FROM customers
WHERE city = 'New York' AND signup_date >= '2023-01-01' AND signup_date < '2024-01-01';

Output:

first_name	last_name	city	signup_date
Alice	Johnson	New York	2023-01-15
David	Chen	New York	2023-08-05

Chaining Multiple AND Conditions

You can chain as many AND conditions as you need. Each additional AND narrows the results further:

SELECT name, price, stock_quantity, is_available
FROM products
WHERE price < 50
  AND stock_quantity > 50
  AND is_available = true;

Output:

name	price	stock_quantity	is_available
Wireless Mouse	29.99	150	true
USB-C Hub	45.00	200	true
Yoga Mat Premium	38.00	100	true
Stainless Water Bottle	24.99	180	true

Every row in this result satisfies all three conditions: the price is below $50, the stock is above 50 units, and the product is available. Rows that fail any single condition are excluded.

How to Think About AND

Each AND condition acts as an additional filter that narrows your results. Start with all rows in the table, and each AND removes the rows that do not match. The more AND conditions you add, the fewer rows you get back.

The OR Operator

OR combines two or more conditions and requires at least one to be true for a row to be included. If any condition passes, the row is in.

SELECT columns
FROM table
WHERE condition1 OR condition2;

Think of OR as a lenient doorman. "Do you meet requirement one? No? Okay, do you meet requirement two? Yes? Come on in." A row only gets excluded if it fails every single condition.

Basic OR Examples

Find customers from New York or Chicago:

SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York' OR city = 'Chicago';

Output:

first_name	last_name	city
Alice	Johnson	New York
Carol	Singh	Chicago
David	Chen	New York
Frank	Wilson	Chicago

A customer only needs to be from one of the two cities to appear in the results.

Find orders that are either pending or shipped:

SELECT id, order_date, total_amount, status
FROM orders
WHERE status = 'pending' OR status = 'shipped';

Output:

id	order_date	total_amount	status
4	2024-03-05	129.99	shipped
5	2024-03-12	155.00	shipped
6	2024-03-20	34.99	pending
7	2024-04-01	63.00	pending
8	2024-04-10	199.98	pending

Chaining Multiple OR Conditions

Like AND, you can chain multiple OR conditions:

SELECT name, price
FROM products
WHERE price = 29.99
   OR price = 45.00
   OR price = 89.99;

Output:

name	price
Wireless Mouse	29.99
Mechanical Keyboard	89.99
USB-C Hub	45.00

Preview: The IN Operator

When you find yourself writing many OR conditions on the same column, SQL offers a cleaner alternative called IN:

-- These two queries produce identical results
WHERE price = 29.99 OR price = 45.00 OR price = 89.99
WHERE price IN (29.99, 45.00, 89.99)

You will learn about IN and other advanced filtering operators in a future guide. For now, chaining OR works perfectly fine.

How to Think About OR

Each OR condition acts as an additional pathway that widens your results. Start with the rows matching the first condition, and each OR adds more rows that match the additional conditions. The more OR conditions you add, the more rows you typically get back. This is the opposite behavior of AND.

The NOT Operator

NOT inverts a condition. It turns true into false and false into true. Whatever the condition would normally match, NOT returns the opposite.

SELECT columns
FROM table
WHERE NOT condition;

Basic NOT Examples

Find all products that are not available:

SELECT name, price, is_available
FROM products
WHERE NOT is_available = true;

Output:

name	price	is_available
Bluetooth Speaker	65.00	false

This is equivalent to writing WHERE is_available = false or WHERE is_available != true, but NOT becomes more powerful when combined with complex conditions.

Find all orders that are not completed:

SELECT id, order_date, status
FROM orders
WHERE NOT status = 'completed';

Output:

id	order_date	status
4	2024-03-05	shipped
5	2024-03-12	shipped
6	2024-03-20	pending
7	2024-04-01	pending
8	2024-04-10	pending

NOT with Other Operators

NOT is especially useful when combined with operators like IN, BETWEEN, LIKE, and IS NULL (which you will learn about in future guides). But even with basic conditions, it provides a clear way to express negative logic:

-- Find customers who are NOT from Seattle
SELECT first_name, last_name, city
FROM customers
WHERE NOT city = 'Seattle';

Output:

first_name	last_name	city
Alice	Johnson	New York
Bob	Martinez	Los Angeles
Carol	Singh	Chicago
David	Chen	New York
Frank	Wilson	Chicago

NOT and NULL: Be Careful

Remember from the previous guide that NULL comparisons behave unexpectedly. The NOT operator does not change this behavior.

-- This does NOT find rows where city is NULL
SELECT first_name, city
FROM customers
WHERE NOT city = 'Seattle';
-- Grace (city is NULL) is MISSING from results

-- Because NOT UNKNOWN is still UNKNOWN, not TRUE
-- NULL = 'Seattle' → UNKNOWN
-- NOT UNKNOWN → UNKNOWN
-- Row excluded

If you need to include NULL values, handle them explicitly:

SELECT first_name, city
FROM customers
WHERE NOT city = 'Seattle' OR city IS NULL;
-- Now Grace (NULL city) IS included

Combining AND, OR, and NOT Together

The real power of logical operators emerges when you combine them. This is also where things can get tricky if you do not understand how SQL evaluates them.

A Simple Combined Example

Find products that are in the Electronics category or cost less than $30, and must be available:

SELECT name, price, category_id, is_available
FROM products
WHERE (category_id = 1 OR price < 30)
  AND is_available = true;

Output:

name	price	category_id	is_available
Wireless Mouse	29.99	1	true
Mechanical Keyboard	89.99	1	true
USB-C Hub	45.00	1	true
Stainless Water Bottle	24.99	4	true

The Bluetooth Speaker is in Electronics (category_id = 1) but is not available, so it is excluded. The Stainless Water Bottle is not in Electronics but costs less than $30 and is available, so it is included.

Operator Precedence: The Hidden Trap

This is the single most important concept in this guide. SQL evaluates AND before OR, just like mathematics evaluates multiplication before addition. If you do not understand this rule, you will write queries that look correct but produce wrong results.

The Precedence Rule

SQL logical operators are evaluated in this order:

1. NOT (evaluated first)
2. AND (evaluated second)
3. OR (evaluated last)

This means AND conditions are grouped and evaluated before OR conditions, even if the OR appears first in your query.

The Problem in Action

Let us say you want to find products that are either in category 1 (Electronics) or category 2 (Books), and the price must be under $40. You might write:

-- This query has a precedence bug
SELECT name, price, category_id
FROM products
WHERE category_id = 1 OR category_id = 2 AND price < 40;

You expect to get Electronics or Books products that cost less than $40. But here is what you actually get:

Output:

name	price	category_id
Wireless Mouse	29.99	1
Mechanical Keyboard	89.99	1
USB-C Hub	45.00	1
SQL for Beginners	34.99	2
Bluetooth Speaker	65.00	1

Wait, the Mechanical Keyboard costs $89.99 and the Bluetooth Speaker costs $65.00. Both exceed $40. Why are they in the results?

Because SQL evaluated the query like this:

-- How SQL actually reads your query (AND binds tighter than OR)
WHERE category_id = 1 OR (category_id = 2 AND price < 40)

SQL grouped the AND conditions first: category_id = 2 AND price < 40. Then it applied OR to that group and category_id = 1. The result is: all Electronics products (regardless of price) OR Books products under $40.

The Fix: Use Parentheses

To get the results you actually want, use parentheses to explicitly group the OR condition:

-- Correct: Parentheses enforce the intended grouping
SELECT name, price, category_id
FROM products
WHERE (category_id = 1 OR category_id = 2) AND price < 40;

Output:

name	price	category_id
Wireless Mouse	29.99	1
SQL for Beginners	34.99	2

Now the query works as intended. The parentheses force SQL to evaluate category_id = 1 OR category_id = 2 first, then apply the AND price < 40 condition to that combined result.

Side-by-Side Comparison

Let us visualize the difference clearly:

-- WITHOUT parentheses (AND evaluated first)
WHERE category_id = 1 OR category_id = 2 AND price < 40

-- SQL interprets this as:
WHERE category_id = 1 OR (category_id = 2 AND price < 40)

-- Result: ALL Electronics + Books under $40
-- Rows: 5

-- WITH parentheses (OR evaluated first, as intended)
WHERE (category_id = 1 OR category_id = 2) AND price < 40

-- SQL interprets this as:
WHERE (category_id = 1 OR category_id = 2) AND price < 40

-- Result: Electronics or Books, but only those under $40
-- Rows: 2

Golden Rule

Always use parentheses when combining AND and OR in the same WHERE clause. Even if you know the precedence rules by heart, parentheses make your intent clear to every developer who reads your query, including your future self.

-- Ambiguous: Relies on precedence rules the reader might not remember
WHERE a = 1 OR b = 2 AND c = 3

-- Clear: Intent is immediately obvious
WHERE a = 1 OR (b = 2 AND c = 3)

-- Also clear: Different intent, also obvious
WHERE (a = 1 OR b = 2) AND c = 3

Another Precedence Example

Find orders that are either pending with a total over $100, or shipped:

-- Without parentheses: WRONG result
SELECT id, total_amount, status
FROM orders
WHERE status = 'pending' AND total_amount > 100 OR status = 'shipped';

SQL reads this as:

WHERE (status = 'pending' AND total_amount > 100) OR status = 'shipped'

Output:

id	total_amount	status
4	129.99	shipped
5	155.00	shipped
8	199.98	pending

This happens to be correct in this case because the AND binds status = 'pending' and total_amount > 100 together, which is what we intended. But what if you actually wanted all pending or shipped orders, but only those over $100?

-- Different intent: All pending or shipped orders over $100
SELECT id, total_amount, status
FROM orders
WHERE (status = 'pending' OR status = 'shipped') AND total_amount > 100;

Output:

id	total_amount	status
4	129.99	shipped
5	155.00	shipped
8	199.98	pending

In this case, both queries happen to produce the same results with this specific data, but they express different logic that would produce different results with different data. The parenthesized version is always clearer about what you mean.

Using Parentheses for Clarity

Even when parentheses are not strictly necessary (because the default precedence already gives you the correct result), adding them improves readability and prevents future mistakes.

Multiple Groups with Parentheses

-- Find products that are:
-- (Electronics OR Sports) AND (price between 25 and 50)
SELECT name, price, category_id
FROM products
WHERE (category_id = 1 OR category_id = 4)
  AND (price >= 25 AND price <= 50);

Output:

name	price	category_id
Wireless Mouse	29.99	1
USB-C Hub	45.00	1
Yoga Mat Premium	38.00	4

The parentheses create two logical groups: one for the category filter and one for the price range. Both groups must be satisfied.

Nested Parentheses

For very complex conditions, you can nest parentheses inside other parentheses:

-- Find orders that are:
-- (completed with total > 100) OR (pending from 2024)
SELECT id, order_date, total_amount, status
FROM orders
WHERE (status = 'completed' AND total_amount > 100)
   OR (status = 'pending' AND order_date >= '2024-01-01');

Output:

id	order_date	total_amount	status
1	2024-01-10	119.98	completed
6	2024-03-20	34.99	pending
7	2024-04-01	63.00	pending
8	2024-04-10	199.98	pending

Each set of parentheses defines a distinct group of conditions. A row matches if it satisfies either complete group.

Complex Multi-Condition Filters

Let us work through several realistic scenarios that combine everything you have learned.

Scenario 1: Inventory Alert Report

Find products that need attention: either out of stock or running low (under 30 units), but only if they are supposed to be available:

SELECT name, stock_quantity, is_available
FROM products
WHERE is_available = true
  AND (stock_quantity = 0 OR stock_quantity < 30);

Output:

name	stock_quantity	is_available
Coffee Maker Pro	25	true

Only the Coffee Maker Pro is marked as available but has low stock. The Bluetooth Speaker has zero stock but is already marked as unavailable, so it does not need an alert.

Scenario 2: Customer Targeting

Find customers for a marketing campaign: target people from New York or Los Angeles who signed up in 2023:

SELECT first_name, last_name, city, signup_date
FROM customers
WHERE (city = 'New York' OR city = 'Los Angeles')
  AND signup_date >= '2023-01-01'
  AND signup_date < '2024-01-01';

Output:

first_name	last_name	city	signup_date
Alice	Johnson	New York	2023-01-15
Bob	Martinez	Los Angeles	2023-03-22
David	Chen	New York	2023-08-05

Scenario 3: Order Processing Dashboard

Find orders that need action: either pending orders over $50 or shipped orders from March 2024:

SELECT id, order_date, total_amount, status
FROM orders
WHERE (status = 'pending' AND total_amount > 50)
   OR (status = 'shipped' AND order_date >= '2024-03-01' AND order_date <= '2024-03-31');

Output:

id	order_date	total_amount	status
4	2024-03-05	129.99	shipped
5	2024-03-12	155.00	shipped
7	2024-04-01	63.00	pending
8	2024-04-10	199.98	pending

Order 6 is pending but only $34.99 (under $50), so it does not match the first group. It also is not shipped, so it does not match the second group. It is correctly excluded.

Scenario 4: Excluding Specific Products

Find all products except those in Electronics (category 1) that are unavailable:

SELECT name, price, category_id, is_available
FROM products
WHERE NOT (category_id = 1 AND is_available = false);

Output:

name	price	category_id	is_available
Wireless Mouse	29.99	1	true
Mechanical Keyboard	89.99	1	true
USB-C Hub	45.00	1	true
SQL for Beginners	34.99	2	true
Data Science Handbook	42.50	2	true
Coffee Maker Pro	129.99	3	true
Yoga Mat Premium	38.00	4	true
Running Shoes X1	110.00	4	true
Stainless Water Bottle	24.99	4	true

The NOT inverts the entire parenthesized condition. The only product that is in Electronics and unavailable is the Bluetooth Speaker, so it is the only one excluded.

Scenario 5: Complex Product Search

Find products that meet any of these criteria, but must have stock above zero:

• Electronics under $50
• Books of any price
• Any product priced over $100

SELECT name, price, category_id, stock_quantity
FROM products
WHERE stock_quantity > 0
  AND (
    (category_id = 1 AND price < 50)
    OR category_id = 2
    OR price > 100
  );

Output:

name	price	category_id	stock_quantity
Wireless Mouse	29.99	1	150
USB-C Hub	45.00	1	200
SQL for Beginners	34.99	2	50
Data Science Handbook	42.50	2	30
Coffee Maker Pro	129.99	3	25
Running Shoes X1	110.00	4	60

The Bluetooth Speaker would match the Electronics under $50 criterion (it is in category 1 and costs $65... actually it does not match), but it has zero stock, so the outer stock_quantity > 0 condition filters it out regardless.

Readability Tips for Complex Conditions

As your WHERE clauses grow, formatting becomes crucial. Here are formatting conventions that keep complex conditions readable.

One Condition Per Line

-- Hard to read
SELECT name, price FROM products WHERE category_id = 1 AND price > 30 AND is_available = true AND stock_quantity > 0;

-- Easy to read
SELECT name, price
FROM products
WHERE category_id = 1
  AND price > 30
  AND is_available = true
  AND stock_quantity > 0;

Indent Grouped Conditions

-- Clear grouping with indentation
SELECT name, price, category_id
FROM products
WHERE stock_quantity > 0
  AND (
    category_id = 1
    OR category_id = 2
    OR category_id = 4
  );

Align OR and AND Operators

-- Well-structured complex query
SELECT id, order_date, total_amount, status
FROM orders
WHERE (status = 'completed' AND total_amount > 100)
   OR (status = 'pending'   AND total_amount > 50)
   OR (status = 'shipped'   AND order_date >= '2024-03-01');

Practical Exercises

Practice combining conditions with these exercises. Write each query yourself before checking the expected output.

Exercise 1

Find all products priced between $30 and $90 (inclusive) that are available.

SELECT name, price, is_available
FROM products
WHERE price >= 30
  AND price <= 90
  AND is_available = true;

Expected output:

name	price	is_available
Mechanical Keyboard	89.99	true
USB-C Hub	45.00	true
SQL for Beginners	34.99	true
Data Science Handbook	42.50	true
Yoga Mat Premium	38.00	true

Exercise 2

Find customers from New York or Seattle.

SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York' OR city = 'Seattle';

Expected output:

first_name	last_name	city
Alice	Johnson	New York
David	Chen	New York
Eva	Brown	Seattle

Exercise 3

Find all orders that are NOT completed AND have a total amount greater than $60.

SELECT id, order_date, total_amount, status
FROM orders
WHERE NOT status = 'completed'
  AND total_amount > 60;

Expected output:

id	order_date	total_amount	status
4	2024-03-05	129.99	shipped
5	2024-03-12	155.00	shipped
7	2024-04-01	63.00	pending
8	2024-04-10	199.98	pending

Exercise 4

Find products that are either in the Books category (2) and cost less than $40, or are in the Sports category (4) and cost less than $30.

SELECT name, price, category_id
FROM products
WHERE (category_id = 2 AND price < 40)
   OR (category_id = 4 AND price < 30);

Expected output:

name	price	category_id
SQL for Beginners	34.99	2
Stainless Water Bottle	24.99	4

Exercise 5

Find all shipped or pending orders placed after March 1, 2024 with a total greater than $100.

SELECT id, order_date, total_amount, status
FROM orders
WHERE (status = 'shipped' OR status = 'pending')
  AND order_date > '2024-03-01'
  AND total_amount > 100;

Expected output:

id	order_date	total_amount	status
4	2024-03-05	129.99	shipped
5	2024-03-12	155	shipped
8	2024-04-10	199.98	pending

Key Takeaways

Logical operators transform simple filters into powerful, precise queries. Here is what you should remember:

• AND requires all conditions to be true and narrows your results
• OR requires at least one condition to be true and widens your results
• NOT inverts a condition, returning the opposite of what it would normally match
• Operator precedence: SQL evaluates NOT first, then AND, then OR
• Always use parentheses when combining AND and OR in the same query to make your intent explicit and avoid precedence bugs
• NOT does not fix NULL behavior: NOT (column = value) still excludes NULL rows
• Format complex conditions with line breaks, indentation, and alignment to keep them readable
• Each AND condition is like adding a filter that removes non-matching rows
• Each OR condition is like adding a pathway that includes additional rows

With SELECT, FROM, WHERE, AND, OR, and NOT in your toolkit, you can now write queries that answer sophisticated business questions. The next step is learning additional filtering tools like IN, BETWEEN, and LIKE that make common patterns even easier to express.