AND, OR, NOT Operators
Combine multiple conditions in your WHERE clause with AND, OR, and NOT.
- AND β All conditions must be true
- OR β At least one condition must be true
- NOT β Negates a condition Master Boolean logic to build precise filters.
What Are Logical Operators?
DfLogical Operators
AND, OR, and NOT are Boolean operators used in the WHERE clause to combine or negate conditions. They control which rows are included in the result set based on whether conditions evaluate to TRUE, FALSE, or UNKNOWN.
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 70000;
AND Operator
AND returns TRUE only when all conditions are TRUE. Use it to narrow results.
DfAND Operator
condition1 AND condition2
Both conditions must be TRUE for the row to be included. If any condition is FALSE or UNKNOWN, the row is excluded.
-- Find active products under $500
SELECT * FROM products
WHERE category = 'Electronics'
AND price < 500
AND stock_quantity > 0;
-- Date range with AND
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date <= '2024-12-31'
AND status != 'Cancelled';
| Condition A | Condition B | A AND B |
|---|---|---|
| TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE |
| FALSE | TRUE | FALSE |
| FALSE | FALSE | FALSE |
OR Operator
OR returns TRUE when any condition is TRUE. Use it to broaden results.
DfOR Operator
condition1 OR condition2
At least one condition must be TRUE for the row to be included. The row is excluded only when all conditions are FALSE or UNKNOWN.
-- Find employees in Engineering or Sales
SELECT * FROM employees
WHERE department = 'Engineering'
OR department = 'Sales';
-- Multiple OR conditions
SELECT * FROM customers
WHERE city = 'New York'
OR city = 'Los Angeles'
OR city = 'Chicago';
WHERE city IN ('New York', 'Los Angeles', 'Chicago').| Condition A | Condition B | A OR B |
|---|---|---|
| TRUE | TRUE | TRUE |
| TRUE | FALSE | TRUE |
| FALSE | TRUE | TRUE |
| FALSE | FALSE | FALSE |
NOT Operator
NOT negates a condition, reversing its Boolean value.
DfNOT Operator
NOT condition
If the condition is TRUE, NOT makes it FALSE. If FALSE, NOT makes it TRUE. UNKNOWN remains UNKNOWN.
-- Exclude Engineering department
SELECT * FROM employees
WHERE NOT department = 'Engineering';
-- NOT with IN
SELECT * FROM products
WHERE NOT category_id IN (1, 2, 3);
-- NOT with EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
);
| Condition | NOT Condition |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| UNKNOWN | UNKNOWN |
Operator Precedence
SQL evaluates operators in a specific order. Understanding precedence prevents unexpected results.
DfPrecedence Order
- NOT (highest)
- AND
- OR (lowest) Use parentheses to override precedence and make intent explicit.
-- Precedence: NOT > AND > OR
-- Without parentheses (AND binds first)
SELECT * FROM employees
WHERE department = 'Engineering'
OR department = 'Sales'
AND salary > 80000;
-- Interpreted as: Engineering OR (Sales AND salary > 80000)
-- With parentheses (clear intent)
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Sales')
AND salary > 80000;
-- Interpreted as: (Engineering OR Sales) AND salary > 80000
-- Complex expression with clear precedence
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Computers')
AND (price < 500 OR price > 2000)
AND NOT status = 'Discontinued';
Combining All Three
Real-world queries often mix AND, OR, and NOT for precise filtering.
-- Find active customers in specific cities who have not placed orders
SELECT * FROM customers
WHERE (city = 'New York' OR city = 'Los Angeles')
AND status = 'Active'
AND NOT customer_id IN (
SELECT DISTINCT customer_id FROM orders
);
-- Find products that are in stock but not in clearance
SELECT * FROM products
WHERE stock_quantity > 0
AND NOT status = 'Discontinued'
AND (category = 'Electronics' OR category = 'Clothing');
Performance Considerations
DfLogical Operator Performance
AND conditions can leverage composite indexes effectively. OR conditions may prevent index usage β consider rewriting with UNION. NOT IN with subqueries can be slow; prefer NOT EXISTS or LEFT JOIN.
-- Slow: OR may bypass index
SELECT * FROM employees
WHERE department = 'Engineering'
OR department = 'Sales';
-- Faster: UNION with indexed columns
SELECT * FROM employees WHERE department = 'Engineering'
UNION
SELECT * FROM employees WHERE department = 'Sales';
| Operator | Index Friendly | Alternative |
|---|---|---|
| AND | Yes | β |
| OR | Sometimes | UNION |
| NOT IN | Sometimes | NOT EXISTS, LEFT JOIN |
| NOT EXISTS | Yes | β |
Practice Exercises
Exercise 1: Find all active Electronics products under $500 with stock.
-- Solution
SELECT * FROM products
WHERE category = 'Electronics'
AND price < 500
AND stock_quantity > 0
AND status = 'Active';
Exercise 2: Find employees who are managers or earn more than $100,000.
-- Solution
SELECT * FROM employees
WHERE job_title = 'Manager'
OR salary > 100000;
Exercise 3: Find customers not from New York who have placed at least one order.
-- Solution
SELECT * FROM customers c
WHERE NOT city = 'New York'
AND EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
);
Summary
Key Takeaways
- AND requires all conditions to be TRUE
- OR requires at least one condition to be TRUE
- NOT negates a condition
- Precedence: NOT > AND > OR β use parentheses to clarify
- OR can prevent index usage; consider UNION as an alternative
- NOT IN with NULLs is problematic; use NOT EXISTS instead
| Operator | Logic | Use Case |
|---|---|---|
| AND | All TRUE | Narrow results |
| OR | Any TRUE | Broaden results |
| NOT | Negate | Exclude rows |
| IN | Value in list | Multiple OR conditions |
| BETWEEN | Range check | Inclusive range |