PostgreSQL WHERE Clause

Overview

The WHERE clause filters records based on specified conditions. It extracts only the records that fulfill a specified condition. The WHERE clause is used with SELECT, UPDATE, and DELETE statements.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Comparison Operators

Equality and Inequality

-- Equality
SELECT * FROM users WHERE status = 'active';
SELECT * FROM products WHERE price = 99.99;

-- Inequality
SELECT * FROM products WHERE price <> 100;
SELECT * FROM products WHERE price != 100;

Comparison Operators

-- Greater than
SELECT * FROM products WHERE price > 50;

-- Greater than or equal
SELECT * FROM products WHERE price >= 50;

-- Less than
SELECT * FROM products WHERE price < 100;

-- Less than or equal
SELECT * FROM products WHERE price <= 100;

BETWEEN Operator

-- Numeric range
SELECT * FROM products WHERE price BETWEEN 10 AND 100;

-- Date range
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 100;

IN Operator

-- List of values
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');

-- Numeric list
SELECT * FROM products WHERE category_id IN (1, 2, 3);

-- Subquery
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = true);

-- NOT IN
SELECT * FROM users WHERE country NOT IN ('USA', 'UK');

LIKE Operator

Pattern Matching

-- Starts with 'J'
SELECT * FROM users WHERE name LIKE 'J%';

-- Ends with '.com'
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Contains 'john'
SELECT * FROM users WHERE name LIKE '%john%';

-- Single character wildcard
SELECT * FROM users WHERE name LIKE '_ohn';  -- Matches 'John', 'Kohn', etc.

-- NOT LIKE
SELECT * FROM users WHERE email NOT LIKE '%@spam.com';

ILIKE (Case-Insensitive)

-- Case-insensitive search
SELECT * FROM users WHERE name ILIKE 'john%';  -- Matches 'John', 'JOHN', 'john'

-- Case-insensitive contains
SELECT * FROM products WHERE name ILIKE '%laptop%';

Logical Operators

AND Operator

The AND operator combines two or more conditions and returns true only when all conditions are true.

-- Two conditions (all must be true)
SELECT * FROM users WHERE status = 'active' AND country = 'USA';

-- Three conditions
SELECT * FROM products 
WHERE category = 'Electronics' 
    AND price > 50 
    AND stock > 0;

-- Multiple AND conditions
SELECT * FROM employees 
WHERE department = 'Sales' 
    AND salary > 50000 
    AND hire_date >= '2020-01-01'
    AND status = 'active';

AND Operator Examples

-- Find orders with specific conditions
SELECT * FROM orders 
WHERE status = 'completed' 
    AND order_date >= '2024-01-01' 
    AND total_amount > 1000;

-- Product filtering
SELECT * FROM products 
WHERE price > 100 
    AND category = 'Electronics' 
    AND stock > 0
    AND rating >= 4.0;

-- User filtering
SELECT * FROM users 
WHERE status = 'active' 
    AND email_verified = true 
    AND created_at >= CURRENT_DATE - INTERVAL '30 days';

OR Operator

The OR operator combines two or more conditions and returns true when at least one condition is true.

-- At least one condition must be true
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Books';

-- Multiple OR conditions
SELECT * FROM users 
WHERE country = 'USA' 
    OR country = 'UK' 
    OR country = 'Canada';

-- Use IN instead of multiple OR (more concise)
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');

OR Operator Examples

-- Find orders with multiple statuses
SELECT * FROM orders 
WHERE status = 'pending' 
    OR status = 'processing' 
    OR status = 'shipped';

-- Better way: Use IN
SELECT * FROM orders 
WHERE status IN ('pending', 'processing', 'shipped');

-- Multiple OR conditions
SELECT * FROM products 
WHERE category = 'Electronics' 
    OR price < 20 
    OR on_sale = true;

NOT Operator

The NOT operator negates a condition, returning records that don't satisfy the condition.

-- Negate condition
SELECT * FROM users WHERE NOT status = 'banned';

-- Equivalent to
SELECT * FROM users WHERE status <> 'banned';

-- NOT with IN
SELECT * FROM users WHERE status NOT IN ('banned', 'suspended');

-- NOT with LIKE
SELECT * FROM users WHERE email NOT LIKE '%@spam.com';

-- NOT with BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 100;

-- NOT with EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Combining AND, OR, NOT

Operator Precedence

Logical operator precedence in PostgreSQL:

1. NOT (highest precedence)
2. AND
3. OR (lowest precedence)

-- Without parentheses (AND has precedence over OR)
SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'Marketing' AND salary > 50000;
-- Equivalent to:
-- WHERE department = 'Sales' OR (department = 'Marketing' AND salary > 50000)

-- Use parentheses to clarify intent
SELECT * FROM employees 
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 50000;

Complex Condition Combinations

-- Use parentheses for precedence
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Books') 
    AND price > 50
    AND stock > 0;

-- Multi-level nested conditions
SELECT * FROM users 
WHERE (country = 'USA' OR country = 'UK')
    AND status = 'active'
    AND NOT email LIKE '%@spam.com'
    AND (age >= 18 OR parental_consent = true);

-- Complex business logic
SELECT * FROM orders 
WHERE (status = 'pending' OR status = 'processing')
    AND (priority = 'high' OR total_amount > 1000)
    AND NOT (payment_method = 'cash' AND shipping_country <> 'USA');

Practical Use Cases

-- Use Case 1: User filtering
SELECT * FROM users 
WHERE (account_type = 'premium' OR account_type = 'enterprise')
    AND status = 'active'
    AND created_at < CURRENT_DATE - INTERVAL '1 year'
    AND (email_verified = true OR phone_verified = true);

-- Use Case 2: Order queries
SELECT * FROM orders 
WHERE (status = 'pending' OR status = 'processing')
    AND priority = 'high'
    AND created_at >= CURRENT_DATE - INTERVAL '7 days'
    AND NOT (shipping_method = 'standard' AND total_amount < 50);

-- Use Case 3: Product search
SELECT * FROM products 
WHERE ((category = 'Electronics' AND price < 500) 
    OR (category = 'Books' AND price < 50))
    AND stock > 0
    AND rating >= 4.0
    AND NOT discontinued = true;

AND/OR Performance Optimization

-- 1. Place highly selective conditions first
-- Good: Filter out most data first
SELECT * FROM orders 
WHERE status = 'completed'  -- Assume only 10% of orders are completed
    AND customer_id = 12345;

-- 2. Use indexes
CREATE INDEX idx_orders_status_customer ON orders(status, customer_id);

-- 3. Avoid complex OR conditions
-- Slow: Multiple OR conditions
SELECT * FROM products 
WHERE category = 'A' OR category = 'B' OR category = 'C';

-- Fast: Use IN
SELECT * FROM products 
WHERE category IN ('A', 'B', 'C');

-- 4. Use UNION instead of complex OR (in some cases)
-- If OR conditions are completely different, consider UNION
SELECT * FROM products WHERE category = 'Electronics' AND price > 1000
UNION
SELECT * FROM products WHERE on_sale = true AND rating >= 4.5;

IS NULL and IS NOT NULL

-- Check for NULL values
SELECT * FROM users WHERE phone IS NULL;

-- Check for non-NULL values
SELECT * FROM users WHERE phone IS NOT NULL;

-- Multiple NULL checks
SELECT * FROM users 
WHERE phone IS NULL 
    AND address IS NULL;

EXISTS Operator

-- Check if subquery returns any rows
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Complex EXISTS
SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.product_id = p.id
        AND oi.quantity > 10
);

ANY and ALL Operators

ANY Operator

-- Greater than any value in subquery
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'Books');

-- Equal to any (similar to IN)
SELECT * FROM users
WHERE country = ANY (ARRAY['USA', 'UK', 'Canada']);

ALL Operator

-- Greater than all values in subquery
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Books');

-- Not equal to all values
SELECT * FROM users
WHERE country <> ALL (ARRAY['Spam', 'Test']);

WHERE with Expressions

-- Arithmetic expressions
SELECT * FROM products WHERE price * 1.1 > 100;

-- String functions
SELECT * FROM users WHERE LENGTH(name) > 10;
SELECT * FROM users WHERE UPPER(email) LIKE '%@GMAIL.COM';

-- Date functions
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;
SELECT * FROM users WHERE AGE(birth_date) > INTERVAL '18 years';

WHERE with CASE

-- Conditional filtering
SELECT * FROM products
WHERE CASE 
    WHEN category = 'Electronics' THEN price > 100
    WHEN category = 'Books' THEN price > 20
    ELSE price > 50
END;

Common Patterns

Multiple Conditions

-- User search with multiple filters
SELECT * FROM users
WHERE status = 'active'
    AND country IN ('USA', 'UK', 'Canada')
    AND created_at >= '2024-01-01'
    AND email LIKE '%@company.com'
    AND age BETWEEN 18 AND 65;

Date Filtering

-- Today's records
SELECT * FROM orders WHERE DATE(created_at) = CURRENT_DATE;

-- Last 7 days
SELECT * FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

-- This month
SELECT * FROM orders 
WHERE EXTRACT(MONTH FROM created_at) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM created_at) = EXTRACT(YEAR FROM CURRENT_DATE);

-- This year
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = EXTRACT(YEAR FROM CURRENT_DATE);

Text Search

-- Case-insensitive search
SELECT * FROM products WHERE LOWER(name) LIKE LOWER('%laptop%');

-- Multiple word search
SELECT * FROM products 
WHERE name ILIKE '%gaming%' 
    AND name ILIKE '%laptop%';

-- Search in multiple columns
SELECT * FROM users 
WHERE name LIKE '%John%' 
    OR email LIKE '%John%';

Null-Safe Comparisons

-- Handle NULL values
SELECT * FROM users 
WHERE COALESCE(phone, '') <> '';

-- IS DISTINCT FROM (NULL-safe comparison)
SELECT * FROM users WHERE email IS DISTINCT FROM NULL;

Performance Tips

-- Use indexes on WHERE columns
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_country ON users(country);

-- Avoid functions on indexed columns (prevents index usage)
-- Bad
SELECT * FROM users WHERE UPPER(email) = 'JOHN@EXAMPLE.COM';

-- Good
SELECT * FROM users WHERE email = 'john@example.com';

-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE status = 'active';

Best Practices

1. Use appropriate operators

   -- Use IN instead of multiple OR
   SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');  -- Good
   SELECT * FROM users WHERE country = 'USA' OR country = 'UK' OR country = 'Canada';  -- Avoid

2. Use BETWEEN for ranges

   -- Use BETWEEN
   SELECT * FROM products WHERE price BETWEEN 10 AND 100;  -- Good
   SELECT * FROM products WHERE price >= 10 AND price <= 100;  -- Also OK

3. Handle NULL properly

   -- Use IS NULL
   SELECT * FROM users WHERE phone IS NULL;  -- Correct
   SELECT * FROM users WHERE phone = NULL;   -- Wrong! Always returns false

4. Use parentheses for clarity

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

Summary

The WHERE clause is essential for filtering data:

• Comparison operators: =, <>, <, >, <=, >=
• Range: BETWEEN
• List: IN
• Pattern: LIKE, ILIKE
• Logical: AND, OR, NOT
• NULL: IS NULL, IS NOT NULL
• Subqueries: EXISTS, ANY, ALL