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';

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';

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
);

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';

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