PostgreSQL WHERE

Key Operators

WHERE price > 100

WHERE age > 18 AND city = 'NYC'

WHERE name LIKE 'John%'

WHERE age BETWEEN 18 AND 65

🔹 Basic WHERE Clause

The WHERE clause uses comparison operators to filter data. You can check if values are equal, not equal, greater than, or less than specific criteria. This is the foundation of data filtering in SQL queries for precise data retrieval.

-- Get products with price greater than 50
SELECT product_name, price 
FROM products 
WHERE price > 50;

-- Get customers from specific country
SELECT name, email 
FROM customers 
WHERE country = 'Canada';

-- Get orders not yet shipped
SELECT order_id, status 
FROM orders 
WHERE status != 'Shipped';

🔹 AND, OR, NOT Operators

Logical operators combine multiple conditions in WHERE clauses. AND requires all conditions to be true, OR requires at least one condition to be true, and NOT negates a condition. These operators enable complex filtering logic for sophisticated queries.

-- AND: Both conditions must be true
SELECT * FROM products 
WHERE category = 'Electronics' AND price < 500;

-- OR: At least one condition must be true
SELECT * FROM customers 
WHERE city = 'New York' OR city = 'Los Angeles';

-- NOT: Negates a condition
SELECT * FROM employees 
WHERE NOT department = 'Sales';

-- Combining operators with parentheses
SELECT * FROM orders 
WHERE (status = 'Pending' OR status = 'Processing') 
AND total > 100;

🔹 LIKE Operator for Pattern Matching

The LIKE operator searches for patterns in text columns using wildcards. The percent sign (%) matches any sequence of characters, while underscore (_) matches exactly one character. This is perfect for searching names, emails, or any text-based data with partial information.

-- Starts with 'John'
SELECT * FROM customers 
WHERE name LIKE 'John%';

-- Ends with 'son'
SELECT * FROM customers 
WHERE name LIKE '%son';

-- Contains 'tech'
SELECT * FROM products 
WHERE description LIKE '%tech%';

-- Second character is 'a'
SELECT * FROM customers 
WHERE name LIKE '_a%';

-- Case-insensitive search with ILIKE (PostgreSQL specific)
SELECT * FROM customers 
WHERE email ILIKE '%gmail.com';

🔹 IN and BETWEEN Operators

The IN operator checks if a value matches any value in a list, simplifying multiple OR conditions. BETWEEN filters values within a range, including the boundary values. Both operators make queries cleaner and more readable when working with multiple values or ranges.

-- IN: Check if value is in a list
SELECT * FROM customers 
WHERE country IN ('USA', 'Canada', 'Mexico');

-- BETWEEN: Check if value is in a range
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500;

-- BETWEEN with dates
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- NOT IN: Exclude values from a list
SELECT * FROM employees 
WHERE department NOT IN ('HR', 'Finance');

🔹 NULL Values

NULL represents missing or unknown data in databases. You cannot use regular comparison operators with NULL; instead, use IS NULL or IS NOT NULL. Understanding NULL handling is crucial for accurate data filtering and avoiding unexpected query results.

-- Find rows with NULL values
SELECT * FROM customers 
WHERE phone IS NULL;

-- Find rows without NULL values
SELECT * FROM customers 
WHERE phone IS NOT NULL;

-- Combining with other conditions
SELECT * FROM orders 
WHERE shipped_date IS NULL AND status = 'Pending';