WHERE Clause Mastery

The WHERE Clause

The WHERE clause filters rows based on specified conditions. Only rows that satisfy the condition are returned.

SELECT column1, column2
FROM table_name
WHERE condition;

💡 WHERE is evaluated before SELECT in SQL execution order. The database filters rows first, then returns the requested columns.

Comparison Operators

SELECT name, price
FROM products
WHERE price >= 25.00
AND price <= 100.00;

Logical Operators

AND

Both conditions must be true.

SELECT * FROM employees
WHERE department = 'Engineering'
AND salary > 80000;

OR

At least one condition must be true.

SELECT * FROM employees
WHERE department = 'Engineering'
OR department = 'Data Science';

NOT

Negates a condition.

SELECT * FROM employees
WHERE NOT department = 'Marketing';

Operator Precedence

NOT  →  AND  →  OR

-- This may not do what you expect:
WHERE department = 'Engineering' OR department = 'Sales' AND salary > 80000

-- Add parentheses for clarity:
WHERE (department = 'Engineering' OR department = 'Sales') AND salary > 80000

⚠️ Always use parentheses when mixing AND and OR to avoid unexpected results. Readability matters.

IN Operator

Matches any value in a list.

-- Without IN (verbose)
WHERE department = 'Engineering'
OR department = 'Sales'
OR department = 'Marketing';

-- With IN (clean)
WHERE department IN ('Engineering', 'Sales', 'Marketing');

-- NOT IN
WHERE department NOT IN ('HR', 'Finance');

BETWEEN Operator

Matches values within a range (inclusive).

-- Date range
WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Numeric range
WHERE salary BETWEEN 50000 AND 100000;

💡 BETWEEN is inclusive on both ends. WHERE salary BETWEEN 50000 AND 100000 includes both 50000 and 100000.

LIKE Operator

Pattern matching with wildcards.

-- Names starting with 'A'
WHERE name LIKE 'A%';

-- Names ending with 'son'
WHERE name LIKE '%son';

-- Names with exactly 5 characters
WHERE name LIKE '_____';

-- Email contains '@company.com'
WHERE email LIKE '%@company.com';

IS NULL / IS NOT NULL

Checks for NULL values.

-- Find employees without a manager
SELECT * FROM employees
WHERE manager_id IS NULL;

-- Find employees with a phone number
SELECT * FROM employees
WHERE phone IS NOT NULL;

⚠️ Never use = NULL or != NULL. NULL represents unknown — use IS NULL and IS NOT NULL instead.

EXISTS

Checks if a subquery returns any rows.

SELECT * FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.department_id
);

Combining Conditions

SELECT
    first_name,
    last_name,
    department,
    salary,
    hire_date
FROM employees
WHERE (department IN ('Engineering', 'Data Science'))
AND (salary BETWEEN 70000 AND 120000)
AND (hire_date >= '2022-01-01')
AND (manager_id IS NOT NULL)
ORDER BY salary DESC;

✏️ Exercise: Write a query to find all products that are either in the 'Electronics' category with price greater than 100, or in the 'Books' category with price less than 50, and are in stock.

See Solution

SELECT *
FROM products
WHERE (
    (category = 'Electronics' AND price > 100)
    OR (category = 'Books' AND price < 50)
)
AND stock_quantity > 0;

✅ Key Takeaways

1. WHERE filters rows before SELECT returns columns
2. Use parentheses with AND/OR to ensure correct logic and readability
3. IN is cleaner than multiple OR conditions for matching lists
4. BOTH BETWEEN is inclusive on both ends
5. Always use IS NULL/IS NOT NULL — never = NULL or != NULL