PostgreSQL AND and OR Operators

Overview

The AND and OR operators combine multiple conditions in a WHERE clause. AND requires all conditions to be true, while OR requires at least one condition to be true.

AND Operator

-- Both conditions must be true
SELECT * FROM users WHERE status = 'active' AND country = 'USA';

-- Multiple AND conditions
SELECT * FROM products 
WHERE category = 'Electronics' 
    AND price > 100 
    AND stock > 0
    AND is_active = TRUE;

OR Operator

-- At least one condition must be true
SELECT * FROM users WHERE country = 'USA' OR country = 'UK';

-- Multiple OR conditions
SELECT * FROM products 
WHERE category = 'Electronics' 
    OR category = 'Books' 
    OR category = 'Clothing';

Combining AND and OR

Precedence

AND has higher precedence than OR, so AND conditions are evaluated first:

-- Without parentheses
-- (category = 'Electronics' OR category = 'Books') AND price > 50
SELECT * FROM products 
WHERE category = 'Electronics' OR category = 'Books' AND price > 50;

-- Using parentheses for clarity
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Books') 
    AND price > 50;

Complex Conditions

-- Complex combination
SELECT * FROM users 
WHERE 
    (status = 'active' OR status = 'pending')
    AND (country = 'USA' OR country = 'Canada')
    AND created_at > '2024-01-01';

-- Three-way combination
SELECT * FROM orders 
WHERE 
    (status = 'completed' OR status = 'shipped')
    AND total > 100
    AND created_at > '2024-01-01';

NOT with AND/OR

-- NOT combined with AND
SELECT * FROM users 
WHERE NOT (status = 'active' AND country = 'USA');

-- NOT combined with OR
SELECT * FROM products 
WHERE NOT (category = 'Electronics' OR category = 'Books');

-- De Morgan's Laws
-- NOT (A AND B) = NOT A OR NOT B
-- NOT (A OR B) = NOT A AND NOT B

Practical Examples

User Filtering

SELECT * FROM users 
WHERE 
    (is_verified = TRUE OR is_admin = TRUE)
    AND status = 'active'
    AND last_login > '2024-01-01';

Product Search

SELECT * FROM products 
WHERE 
    (name ILIKE '%laptop%' OR name ILIKE '%computer%' OR name ILIKE '%pc%')
    AND (category = 'Electronics' OR category = 'Computers')
    AND (price BETWEEN 500 AND 2000 OR price IS NULL)
    AND stock > 0;

Order Filtering

SELECT * FROM orders 
WHERE 
    (status = 'completed' OR status = 'delivered')
    AND (total > 50 OR discount > 0)
    AND created_at BETWEEN '2024-01-01' AND '2024-12-31';

Summary

AND and OR operators:

• AND: All conditions must be true
• OR: At least one condition must be true
• Precedence: AND is evaluated before OR
• Use parentheses to improve code clarity