SQL - WHERE Clause with Examples | Application Architect

Key Insights

The WHERE clause filters rows based on specified conditions, executing before GROUP BY and aggregate functions in SQL’s logical query processing order
Combining multiple conditions with AND, OR, and NOT operators requires understanding operator precedence—AND evaluates before OR unless parentheses override the default order
NULL values demand special handling with IS NULL and IS NOT NULL operators since standard comparison operators (=, !=) cannot evaluate NULL correctly

Basic WHERE Clause Syntax

The WHERE clause filters records that meet specific criteria. It appears after the FROM clause and before GROUP BY, HAVING, or ORDER BY clauses.

SELECT column1, column2
FROM table_name
WHERE condition;

Here’s a practical example using an employees table:

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 75000;

This returns only employees earning more than $75,000. The WHERE clause evaluates each row individually, including only those where the condition evaluates to TRUE.

Comparison Operators

SQL supports standard comparison operators for numeric, string, and date comparisons:

-- Numeric comparisons
SELECT product_name, price
FROM products
WHERE price >= 100 AND price <= 500;

-- String comparison (case-sensitive in most databases)
SELECT customer_id, country
FROM customers
WHERE country = 'Germany';

-- Date comparison
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date >= '2024-01-01';

-- Not equal (both syntaxes work)
SELECT employee_id, department
FROM employees
WHERE department != 'Sales';
-- OR
WHERE department <> 'Sales';

Combining Multiple Conditions

Use AND, OR, and NOT to create complex filtering logic. Understanding operator precedence prevents unexpected results.

-- AND: Both conditions must be true
SELECT product_name, category, price
FROM products
WHERE category = 'Electronics' 
  AND price < 1000;

-- OR: At least one condition must be true
SELECT customer_id, city, country
FROM customers
WHERE country = 'USA' 
   OR country = 'Canada';

-- Combining AND/OR requires parentheses for clarity
SELECT product_name, category, price, stock_quantity
FROM products
WHERE (category = 'Electronics' OR category = 'Computers')
  AND price < 500
  AND stock_quantity > 0;

Without parentheses in the last example, the query would produce incorrect results because AND has higher precedence than OR:

-- WRONG: Returns Electronics under $500 OR all Computers with stock
SELECT product_name, category, price, stock_quantity
FROM products
WHERE category = 'Electronics' 
   OR category = 'Computers'
  AND price < 500
  AND stock_quantity > 0;

IN Operator for Multiple Values

The IN operator simplifies checking against multiple values, replacing multiple OR conditions:

-- Without IN (verbose)
SELECT employee_id, first_name, department
FROM employees
WHERE department = 'Sales' 
   OR department = 'Marketing' 
   OR department = 'HR';

-- With IN (cleaner)
SELECT employee_id, first_name, department
FROM employees
WHERE department IN ('Sales', 'Marketing', 'HR');

-- NOT IN to exclude values
SELECT product_name, category
FROM products
WHERE category NOT IN ('Discontinued', 'Obsolete');

-- IN with subquery
SELECT customer_id, company_name
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id 
    FROM orders 
    WHERE order_date >= '2024-01-01'
);

BETWEEN Operator for Ranges

BETWEEN provides inclusive range filtering for numbers, dates, and strings:

-- Numeric range (inclusive)
SELECT product_name, price
FROM products
WHERE price BETWEEN 50 AND 200;
-- Equivalent to: price >= 50 AND price <= 200

-- Date range
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- NOT BETWEEN
SELECT employee_id, salary
FROM employees
WHERE salary NOT BETWEEN 40000 AND 80000;

LIKE Operator for Pattern Matching

LIKE enables partial string matching using wildcards: % (zero or more characters) and _ (exactly one character).

-- Starts with 'A'
SELECT customer_id, company_name
FROM customers
WHERE company_name LIKE 'A%';

-- Ends with 'Inc'
SELECT company_name
FROM customers
WHERE company_name LIKE '%Inc';

-- Contains 'tech'
SELECT company_name
FROM customers
WHERE company_name LIKE '%tech%';

-- Second character is 'a'
SELECT first_name
FROM employees
WHERE first_name LIKE '_a%';

-- Exactly 5 characters
SELECT product_code
FROM products
WHERE product_code LIKE '_____';

-- Case-insensitive search (ILIKE in PostgreSQL)
SELECT email
FROM users
WHERE email ILIKE '%@GMAIL.COM';

For literal % or _ characters, use escape sequences:

SELECT description
FROM products
WHERE description LIKE '%50\%%' ESCAPE '\';
-- Finds "50%" in the description

Handling NULL Values

NULL represents missing or unknown data. Standard comparison operators don’t work with NULL—use IS NULL or IS NOT NULL instead:

-- WRONG: Returns no results even if NULLs exist
SELECT employee_id, phone_number
FROM employees
WHERE phone_number = NULL;

-- CORRECT: Use IS NULL
SELECT employee_id, phone_number
FROM employees
WHERE phone_number IS NULL;

-- Find records with values
SELECT employee_id, email
FROM employees
WHERE email IS NOT NULL;

-- Combining NULL checks with other conditions
SELECT product_name, discontinued_date
FROM products
WHERE discontinued_date IS NULL
  AND category = 'Electronics';

Advanced Filtering Techniques

Combining various operators creates powerful filtering logic:

-- Complex business logic
SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount,
    status
FROM orders
WHERE (status = 'Pending' OR status = 'Processing')
  AND order_date >= CURRENT_DATE - INTERVAL '30 days'
  AND total_amount > 100
  AND customer_id IN (
      SELECT customer_id 
      FROM customers 
      WHERE loyalty_tier = 'Gold'
  )
  AND shipping_country NOT IN ('Restricted1', 'Restricted2');

-- Using expressions in WHERE
SELECT product_name, price, quantity
FROM products
WHERE price * quantity > 10000;

-- Date functions in WHERE
SELECT employee_id, hire_date
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) = 2023
  AND EXTRACT(MONTH FROM hire_date) IN (1, 2, 3);

Performance Considerations

WHERE clause efficiency impacts query performance significantly:

-- Use indexed columns when possible
SELECT customer_id, order_date
FROM orders
WHERE customer_id = 12345  -- Fast if customer_id is indexed
  AND order_date >= '2024-01-01';

-- Avoid functions on indexed columns (prevents index usage)
-- SLOW:
WHERE UPPER(email) = 'USER@EXAMPLE.COM'
-- BETTER:
WHERE email = 'user@example.com'

-- Avoid leading wildcards when possible
-- SLOW (full table scan):
WHERE product_name LIKE '%widget'
-- FASTER (can use index):
WHERE product_name LIKE 'widget%'

Common Pitfalls

Watch for these frequent mistakes:

-- String comparison case sensitivity
-- May miss 'germany', 'GERMANY', etc.
WHERE country = 'Germany'

-- Incorrect NULL handling
WHERE column = NULL  -- Always returns no rows

-- Missing parentheses with mixed AND/OR
WHERE a = 1 OR b = 2 AND c = 3  -- Might not mean what you think

-- Type mismatches
WHERE numeric_id = '123'  -- Works but prevents index usage in some databases

The WHERE clause forms the foundation of data filtering in SQL. Master these patterns to write efficient, maintainable queries that retrieve exactly the data you need.