Filtering Data

## Learning Objectives - Master WHERE clauses - Use comparison operators - Work with logical operators - Handle NULL in filtering ## WHERE Clause ### Basic Comparison ```sql SELECT first_name, last_name, salary FROM employees WHERE salary > 60000; ``` ### Multiple Conditions ```sql SELECT first_name, last_name, department FROM employees WHERE department = 'Sales'; ``` ## Comparison Operators | Operator | Description | |----------|-------------| | = | Equal | | <> or != | Not equal | | > | Greater than | | < | Less than | | >= | Greater than or equal | | <= | Less than or equal | ### Examples ```sql -- Salary equals 75000 WHERE salary = 75000 -- Salary not equal to 50000 WHERE salary <> 50000 -- Salary greater than 50000 WHERE salary > 50000 -- Salary between (inclusive) WHERE salary >= 50000 AND salary <= 80000 ``` ## Logical Operators ### AND ```sql SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000; ``` ### OR ```sql SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing'; ``` ### NOT ```sql SELECT * FROM employees WHERE NOT department = 'Sales'; -- Same as WHERE department <> 'Sales'; ``` ### Operator Precedence ```sql -- AND has higher precedence than OR WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 60000 ``` ## IN Operator ### Single Column IN ```sql SELECT * FROM employees WHERE department IN ('Sales', 'Marketing', 'HR'); ``` ### NOT IN ```sql SELECT * FROM employees WHERE department NOT IN ('Sales', 'Marketing'); ``` ## BETWEEN Operator ### Range Check ```sql SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000; -- Equivalent to WHERE salary >= 50000 AND salary <= 80000; ``` ### NOT BETWEEN ```sql SELECT * FROM employees WHERE salary NOT BETWEEN 50000 AND 80000; ``` ## LIKE Operator ### Pattern Matching ```sql -- Names starting with 'A' WHERE first_name LIKE 'A%' -- Names containing 'on' WHERE first_name LIKE '%on%' -- Names ending with 's' WHERE last_name LIKE '%s' ``` ### Wildcards | Wildcard | Description | |----------|-------------| | % | Any sequence of characters | | _ | Single character | ### LIKE Examples ```sql -- Starts with A, second char any, rest 'hn' WHERE first_name LIKE 'A_n%' -- Contains 'o' second character WHERE first_name LIKE '_o%' -- Email ending with .com WHERE email LIKE '%.com' ``` ### NOT LIKE ```sql SELECT * FROM employees WHERE first_name NOT LIKE 'A%'; ``` ## ILIKE (PostgreSQL) Case-insensitive pattern matching: ```sql -- Case insensitive WHERE first_name ILIKE 'alice' WHERE first_name ILIKE '%ALICE%' ``` ## Handling NULL in WHERE ### IS NULL ```sql SELECT * FROM employees WHERE email IS NULL; ``` ### IS NOT NULL ```sql SELECT * FROM employees WHERE email IS NOT NULL; ``` ### Common Mistake ```sql -- WRONG - returns nothing WHERE email = NULL -- CORRECT WHERE email IS NULL ``` ## Pattern Examples ### Phone Numbers ```sql -- (555) format WHERE phone LIKE '(___) ___-____' -- Starts with 555 WHERE phone LIKE '555%' ``` ### Dates ```sql -- Hired in 2020 WHERE hire_date LIKE '2020%' -- Or for proper date handling: WHERE hire_date >= '2020-01-01' AND hire_date <= '2020-12-31' ``` ## Combining Filters ### Complex WHERE ```sql SELECT first_name, last_name, salary, department FROM employees WHERE (department IN ('Sales', 'Marketing', 'Engineering')) AND salary BETWEEN 50000 AND 100000 AND (first_name LIKE 'A%' OR last_name LIKE 'S%') AND email IS NOT NULL; ``` ## Summary - WHERE filters rows based on conditions - Comparison operators: =, <>, <, >, <=, >= - Combine with AND, OR, NOT - IN checks membership in a list - BETWEEN checks ranges (inclusive) - LIKE does pattern matching with % and _ - IS NULL / IS NOT NULL for NULL values - Always use parentheses for complex conditions