SQL Fundamentals
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?
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.
-- 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.
-- 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';
| 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.
-- 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.
-- 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
-- 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
| 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 |