Module 9 — WHERE

1. WHERE — Basic Filtering

What it is: The WHERE clause filters rows based on conditions — only rows that match the condition are included in the result. It's applied before grouping, aggregation, and ordering.

Why we use it: Without WHERE, queries return every row in the table. In real applications, you almost always need a subset — active users, recent orders, products in a price range, etc.

When we use it: Every time you need to filter data — user lookups, search functionality, report generation, data analysis, or any query that needs a specific subset of rows.

-- Find users older than 25
SELECT * FROM users WHERE age > 25;
 
-- Find active users
SELECT * FROM users WHERE is_active = true;
 
-- Find products in a specific category
SELECT * FROM products WHERE category = 'Electronics';

2. Comparison Operators

What it is: Comparison operators compare values in a column against a literal value or another column. They return true, false, or NULL, and are the building blocks of WHERE conditions.

Why we use it: Every filter needs a comparison — "is the price greater than 100?", "is the status active?", "is the date after January 1st?". These operators express those comparisons.

When we use it: In every WHERE clause that filters by value — numeric ranges, text matching, date comparisons, etc.

3. Logical Operators (AND, OR, NOT)

What it is: Logical operators combine multiple conditions in a WHERE clause. AND requires all conditions to be true, OR requires at least one to be true, and NOT negates a condition.

Why we use it: Real-world filters are rarely single conditions — "active users over 18", "electronics or accessories under $50", "not deleted". Logical operators express these complex conditions.

When we use it: Every time you need to combine multiple filter conditions — multi-criteria search, complex business rules, or data analysis with multiple constraints.

Combine multiple conditions:

-- Both must be true
SELECT * FROM products
WHERE category = 'Electronics' AND price < 5000;
 
-- Either can be true
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Accessories';
 
--

Operator Precedence

NOT > AND > OR — Use parentheses to be explicit.

-- Without parentheses (may not do what you expect)
SELECT * FROM products
WHERE category = 'Electronics' AND price < 5000 OR brand = 'TechCorp';
 
-- With parentheses (clear intent)
SELECT * FROM products
WHERE (category = 'Electronics' AND price <

4. BETWEEN — Range Filtering

What it is: BETWEEN is a shorthand for "greater than or equal to AND less than or equal to". It filters rows where a column's value falls within a specified range (inclusive on both ends).

Why we use it: Instead of writing price >= 100 AND price <= 500, you can write price BETWEEN 100 AND 500. It's cleaner, more readable, and less error-prone.

When we use it: When filtering by numeric ranges (prices, ages, scores), date ranges (last 30 days, this month), or any continuous value range.

-- Instead of: price >= 100 AND price <= 500
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
 
-- Works with dates too
SELECT * FROM posts
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

5. IN — List Filtering

What it is: IN checks if a column's value matches any value in a provided list. It's a shorthand for multiple OR conditions — category IN ('A', 'B', 'C') is the same as category = 'A' OR category = 'B' OR category = 'C'.

Why we use it: When filtering against a known set of values, IN is cleaner and more readable than multiple OR conditions. It's also easier to maintain — adding a new value is just adding to the list.

When we use it: When filtering by a set of known values — categories, statuses, IDs, countries, etc.

-- Instead of: category = 'Electronics' OR category = 'Accessories' OR category = 'Gadgets'
SELECT * FROM products
WHERE category IN ('Electronics', 'Accessories', 'Gadgets');
 
-- Negate with NOT IN
SELECT * FROM products
WHERE category NOT IN ('Discontinued', '

6. IS NULL — Checking for Missing Data

What it is: IS NULL checks if a column contains no value (NULL). NULL is special in SQL — it's not zero, not an empty string, and not false. It means "unknown" or "not provided". You cannot use = NULL — it doesn't work.

Why we use it: Many columns are optional (nullable) — bio, phone, description. Finding rows where these are missing (or present) is a common query pattern.

When we use it: When finding incomplete records (users without profiles), checking for missing data, or filtering out rows with optional fields.

-- WRONG: returns nothing
SELECT * FROM users WHERE bio = NULL;
 
-- RIGHT:
SELECT * FROM users WHERE bio IS NULL;
SELECT * FROM users WHERE bio IS NOT NULL;

Common Mistakes to Avoid

1. Using = NULL instead of IS NULL — This is the #1 SQL beginner mistake
2. Forgetting parentheses with OR — A AND B OR C is not the same as A AND (B OR C)
3. Using BETWEEN with dates incorrectly — Timezone issues can cause unexpected results

Practice Exercise

Using your courses table:

1. Find all courses with a price greater than 2000
2. Find all courses that are published AND have a price less than 5000
3. Find all courses by instructor 'John Doe' OR 'Jane Smith' using IN
4. Find all courses with a price between 1000 and 5000
5. Find all courses where the instructor is NULL
6. Find all courses that are NOT published