TECH **SQL for Data Analysis: Filtering Mastery (AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL)**

SQL for Data Analysis: Filtering Mastery (AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL)

Zero-fluff, hyper-practical guide for real projects and certifications (PL-300, Google Data Analytics, etc.)

1. What This Is & Why It Matters

You’re analyzing customer data for an e-commerce company. Your boss asks: "Show me all high-value customers (spending > $1,000) who haven’t made a purchase in the last 6 months, live in California or New York, and whose email isn’t from a free provider (e.g., Gmail, Yahoo)."

Without filtering operators, you’d manually scroll through thousands of rows. With them, you write one SQL query that returns the exact list in seconds.

Why this matters in production:
- Performance: Poor filtering = full table scans = slow queries = angry users.
- Accuracy: Missing a NOT or misplacing parentheses in AND/OR = wrong results = bad business decisions.
- Maintainability: Complex filters in legacy code become "spaghetti SQL" if not structured clearly.

Real-world scenario:
You inherit a dashboard that’s timing out. The query fetches all orders, then filters in Python. Fix it by pushing filters to SQL (where they belong) using WHERE, IN, BETWEEN, etc. Instant 10x speedup.

2. Core Concepts & Components

WHERE

• Definition: The foundation of filtering. Specifies conditions rows must meet to be included.
• Production insight: Always filter as early as possible (in SQL, not in your app). Reduces data transfer and processing time.

AND

• Definition: Both conditions must be true.
• Example: WHERE age > 30 AND country = 'USA'
• Production insight: Parentheses matter! WHERE (age > 30 AND country = 'USA') OR status = 'VIP' is different from WHERE age > 30 AND (country = 'USA' OR status = 'VIP').

OR

• Definition: Either condition can be true.
• Example: WHERE country = 'USA' OR country = 'Canada'
• Production insight: OR can kill performance. For long lists, use IN instead (e.g., WHERE country IN ('USA', 'Canada')).

NOT

• Definition: Negates a condition.
• Example: WHERE NOT country = 'USA' (same as WHERE country != 'USA')
• Production insight: NOT with NULL is tricky. WHERE NOT (age > 30) includes NULL ages, but WHERE age <= 30 does not.

IN

• Definition: Checks if a value matches any in a list.
• Example: WHERE country IN ('USA', 'Canada', 'Mexico')
• Production insight: For large lists, consider a temporary table or EXISTS for better performance.

BETWEEN

• Definition: Checks if a value is within a range (inclusive).
• Example: WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
• Production insight: BETWEEN is inclusive. BETWEEN 1 AND 10 includes 1 and 10. For exclusive ranges, use > 1 AND < 10.

LIKE

• Definition: Pattern matching with wildcards (% for any characters, _ for a single character).
• Example: WHERE email LIKE '%@gmail.com' (ends with @gmail.com)
• Production insight: LIKE with leading wildcards (%gmail) can’t use indexes. Avoid in large tables.

IS NULL / IS NOT NULL

• Definition: Checks for NULL values (missing/unknown data).
• Example: WHERE email IS NULL
• Production insight: NULL ≠ empty string (''). WHERE email = '' is different from WHERE email IS NULL.

Operator Precedence

• Order: NOT > AND > OR
• Example: WHERE age > 30 OR country = 'USA' AND status = 'VIP' is evaluated as WHERE age > 30 OR (country = 'USA' AND status = 'VIP').
• Production insight: Always use parentheses to avoid ambiguity.

3. Step-by-Step Hands-On: Filtering a Customer Dataset

Task: Write a query to find: - Customers from California or New York.
- Who spent between $500 and $5,000 in the last year.
- Whose email isn’t from a free provider (e.g., Gmail, Yahoo).
- Who haven’t made a purchase in the last 3 months.

Prerequisites:
- A SQL environment (e.g., PostgreSQL, MySQL, BigQuery, or SQLite).
- Sample data (use this schema): ```sql CREATE TABLE customers (
customer_id INT,
name VARCHAR(100),
email VARCHAR(100),
state VARCHAR(2),
total_spent DECIMAL(10,2),
last_purchase_date DATE );

INSERT INTO customers VALUES (1, 'Alice', '[email protected]', 'CA', 1200.50, '2023-10-15'), (2, 'Bob', '[email protected]', 'NY', 3000.00, '2023-12-01'), (3, 'Charlie', '[email protected]', 'TX', 200.00, '2023-11-20'), (4, 'Dana', '[email protected]', 'CA', 750.00, '2023-08-10'), (5, 'Eve', NULL, 'NY', 5000.00, '2023-07-01'); ```

Step 1: Filter by State (IN)

SELECT *
FROM customers
WHERE state IN ('CA', 'NY');

Output:
| customer_id | name | email | state | total_spent | last_purchase_date | |-------------|-------|----------------|-------|-------------|--------------------| | 1 | Alice | [email protected]| CA | 1200.50 | 2023-10-15 | | 2 | Bob | [email protected]| NY | 3000.00 | 2023-12-01 | | 4 | Dana | [email protected] | CA | 750.00 | 2023-08-10 | | 5 | Eve | NULL | NY | 5000.00 | 2023-07-01 |

Step 2: Add Spending Range (BETWEEN)

SELECT *
FROM customers
WHERE state IN ('CA', 'NY')
  AND total_spent BETWEEN 500 AND 5000;

Output: Same as above (all rows meet the condition).

Step 3: Exclude Free Email Providers (NOT LIKE)

SELECT *
FROM customers
WHERE state IN ('CA', 'NY')
  AND total_spent BETWEEN 500 AND 5000
  AND email NOT LIKE '%@gmail.com'
  AND email NOT LIKE '%@yahoo.com';

Output:
| customer_id | name | email | state | total_spent | last_purchase_date | |-------------|------|---------------|-------|-------------|--------------------| | 2 | Bob | [email protected]| NY | 3000.00 | 2023-12-01 | | 4 | Dana | [email protected] | CA | 750.00 | 2023-08-10 |

Step 4: Filter by Last Purchase Date (BETWEEN + NOT)

SELECT *
FROM customers
WHERE state IN ('CA', 'NY')
  AND total_spent BETWEEN 500 AND 5000
  AND email NOT LIKE '%@gmail.com'
  AND email NOT LIKE '%@yahoo.com'
  AND last_purchase_date < '2023-10-01'; -- No purchases in last 3 months (assuming today is 2024-01-01)

Output:
| customer_id | name | email | state | total_spent | last_purchase_date | |-------------|------|---------------|-------|-------------|--------------------| | 4 | Dana | [email protected] | CA | 750.00 | 2023-08-10 |

Step 5: Handle NULL Emails (IS NOT NULL)

SELECT *
FROM customers
WHERE state IN ('CA', 'NY')
  AND total_spent BETWEEN 500 AND 5000
  AND (email NOT LIKE '%@gmail.com' OR email IS NULL)
  AND (email NOT LIKE '%@yahoo.com' OR email IS NULL)
  AND last_purchase_date < '2023-10-01';

Output: Now includes Eve (who has a NULL email).
| customer_id | name | email | state | total_spent | last_purchase_date | |-------------|------|-------|-------|-------------|--------------------| | 4 | Dana | [email protected] | CA | 750.00 | 2023-08-10 | | 5 | Eve | NULL | NY | 5000.00 | 2023-07-01 |

4. ? Production-Ready Best Practices

Performance

• Use IN instead of multiple ORs: WHERE country IN ('USA', 'Canada') is faster than WHERE country = 'USA' OR country = 'Canada'.
• Avoid leading wildcards in LIKE: LIKE '%gmail' can’t use indexes. Use LIKE 'gmail%' if possible.
• Filter early: Push filters to the database (not your app) to reduce data transfer.

Readability

• Use parentheses for AND/OR: WHERE (age > 30 AND country = 'USA') OR status = 'VIP' is clearer than WHERE age > 30 AND country = 'USA' OR status = 'VIP'.
• Indent complex filters:
  sql WHERE
state IN ('CA', 'NY')
AND total_spent BETWEEN 500 AND 5000
AND (
email NOT LIKE '%@gmail.com'
OR email IS NULL
)

Handling NULL

• Never use = NULL: Use IS NULL or IS NOT NULL.
• NULL in NOT IN: WHERE id NOT IN (1, 2, NULL) returns no rows (because NULL is unknown). Use NOT EXISTS instead.

Security

• Sanitize inputs: If building dynamic SQL (e.g., in Python), use parameterized queries to avoid SQL injection: ```python # BAD: Vulnerable to injection query = f"SELECT * FROM users WHERE email = '{user_input}'"

# GOOD: Parameterized cursor.execute("SELECT * FROM users WHERE email = %s", (user_input,)) ```

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical question patterns:
1. AND/OR precedence:
- Question: What does WHERE age > 30 OR country = 'USA' AND status = 'VIP' return?
- Trap: Without parentheses, AND takes precedence. Correct answer: WHERE age > 30 OR (country = 'USA' AND status = 'VIP').
- Key: Always use parentheses to avoid ambiguity.

1. NULL handling:
2. Question: Which query returns rows where email is not Gmail?
   • A) WHERE email != '%@gmail.com'
   • B) WHERE email NOT LIKE '%@gmail.com'
   • C) WHERE email NOT LIKE '%@gmail.com' OR email IS NULL

3. Correct answer: C (because NULL emails won’t match NOT LIKE).

4. IN vs OR:

5. Question: Which is more efficient for checking 10 countries?
   • A) WHERE country = 'USA' OR country = 'Canada' OR ...
   • B) WHERE country IN ('USA', 'Canada', ...)

6. Correct answer: B (same logic, better performance).

7. BETWEEN inclusivity:

8. Question: Does BETWEEN 1 AND 10 include 1 and 10?
9. Answer: Yes (it’s inclusive).

7. ? Hands-On Challenge

Challenge:
Write a query to find all products that: - Are in the "Electronics" or "Appliances" category.
- Have a price between $100 and $1,000.
- Have a name containing "Pro" (case-insensitive).
- Are not discontinued (discontinued = FALSE).

Solution:

SELECT *
FROM products
WHERE
    category IN ('Electronics', 'Appliances')
    AND price BETWEEN 100 AND 1000
    AND LOWER(name) LIKE '%pro%'
    AND discontinued = FALSE;

Why it works:
- IN efficiently checks multiple categories.
- BETWEEN includes the boundary prices.
- LOWER(name) LIKE '%pro%' handles case insensitivity.
- discontinued = FALSE filters out discontinued products.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. SQLZoo – Interactive SQL Tutorial (Practice WHERE clauses interactively).
2. PostgreSQL Documentation – Logical Operators (Official docs for AND/OR/NOT).
3. Mode Analytics SQL Tutorial (Real-world SQL examples).
4. Book: SQL for Data Analysis by O’Reilly (Chapter 3: Filtering Data).