🔍 SQL Filtering: Finding Exactly What You Need

The Magic Library Analogy

Imagine you’re in the biggest library in the world. It has millions of books! Now, what if you only want:

• Books written after 2020?
• Mystery novels?
• Books by your favorite author?

You’d need a super-smart librarian who can filter through everything and bring you exactly what you want.

That’s what SQL filtering does! It’s your super-smart librarian for data.

🎯 The WHERE Clause: Your Filter Button

What is WHERE?

Think of WHERE as saying “but only if…”

Without WHERE, SQL gives you EVERYTHING:

SELECT * FROM books;

“Give me ALL the books!” (That’s millions of books! 😱)

With WHERE, you get just what you need:

SELECT * FROM books
WHERE genre = 'Mystery';

“Give me books, but only if they’re Mystery!” ✨

The Simple Pattern

SELECT columns
FROM table_name
WHERE condition;

Real Example:

SELECT name, age
FROM students
WHERE age = 10;

“Show me names and ages of students who are exactly 10 years old.”

⚖️ Comparison Operators: The “Is It True?” Tools

These operators compare two values and ask: “Is this true?”

Easy Examples

Equal (=)

SELECT * FROM toys
WHERE color = 'blue';

“Find toys that are blue.”

Not Equal (!=)

SELECT * FROM fruits
WHERE name != 'apple';

“Find fruits that are NOT apples.”

Greater Than (>)

SELECT * FROM games
WHERE players > 2;

“Find games for more than 2 players.”

Less Than or Equal (<=)

SELECT * FROM snacks
WHERE calories <= 100;

“Find snacks with 100 calories or less.”

🔗 Logical Operators: Combining Conditions

Sometimes one filter isn’t enough. You need to combine them!

AND: Both Must Be True

Like saying “I want ice cream AND chocolate sauce”—you need BOTH!

SELECT * FROM pets
WHERE type = 'dog' AND age < 3;

“Find dogs that are also younger than 3 years.”

OR: At Least One Must Be True

Like saying “I’ll have pizza OR pasta”—either one works!

SELECT * FROM clothes
WHERE color = 'red' OR color = 'blue';

“Find clothes that are red or blue.”

NOT: Flip It Around

Like saying “anything BUT broccoli!”

SELECT * FROM vegetables
WHERE NOT name = 'broccoli';

“Find vegetables that are NOT broccoli.”

Combining Multiple Conditions

SELECT * FROM movies
WHERE rating = 'PG'
  AND (genre = 'Comedy' OR genre = 'Adventure');

“Find PG movies that are either Comedy or Adventure.”

📊 Operator Precedence: Who Goes First?

When you mix operators, SQL follows a pecking order:

graph TD
    A[1. Parentheses] --> B[2. NOT]
    B --> C[3. AND]
    C --> D[4. OR]

Why It Matters

Without parentheses:

SELECT * FROM toys
WHERE color = 'red' OR color = 'blue' AND price < 10;

SQL reads this as:

• color = 'red' OR (color = 'blue' AND price < 10)

With parentheses (clearer!):

SELECT * FROM toys
WHERE (color = 'red' OR color = 'blue') AND price < 10;

“Red or blue toys, but must be under $10.”

🌟 Golden Rule

When in doubt, use parentheses! They make your intent crystal clear.

📏 BETWEEN: The Range Finder

Instead of writing >= 5 AND <= 10, use BETWEEN!

The Simple Way

SELECT * FROM students
WHERE age BETWEEN 8 AND 12;

“Find students aged 8, 9, 10, 11, or 12.”

Key Point: BETWEEN includes both endpoints (8 and 12).

More Examples

Numbers:

SELECT * FROM products
WHERE price BETWEEN 10 AND 50;

Dates:

SELECT * FROM events
WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31';

NOT BETWEEN:

SELECT * FROM scores
WHERE points NOT BETWEEN 0 AND 50;

“Find scores that are NOT between 0 and 50.”

📋 IN: The Checklist Matcher

Instead of writing many OR conditions, use IN!

The Long Way (tedious)

SELECT * FROM fruits
WHERE name = 'apple'
   OR name = 'banana'
   OR name = 'orange';

The Smart Way (with IN)

SELECT * FROM fruits
WHERE name IN ('apple', 'banana', 'orange');

“Is the name on my list? Apple, banana, or orange?” ✅

More Examples

Numbers:

SELECT * FROM orders
WHERE status_id IN (1, 3, 5);

NOT IN:

SELECT * FROM colors
WHERE name NOT IN ('black', 'white', 'gray');

“Find colors that are NOT black, white, or gray.”

🔎 LIKE: The Pattern Detective

LIKE is your search tool. It finds things that match a pattern.

Meet the Wildcards

Pattern Examples

Starts with…

SELECT * FROM names
WHERE name LIKE 'J%';

“Names starting with J” → John, Jane, James

Ends with…

SELECT * FROM files
WHERE filename LIKE '%.pdf';

“Files ending with .pdf”

Contains…

SELECT * FROM products
WHERE description LIKE '%chocolate%';

“Products with ‘chocolate’ anywhere”

Exact Length Pattern

SELECT * FROM codes
WHERE code LIKE 'A__';

“Codes: A + any 2 characters” → A12, ABC, A99

Combining Wildcards

SELECT * FROM emails
WHERE email LIKE '%@gmail.%';

“Find Gmail addresses”

Case Sensitivity Note

• Some databases: LIKE is case-sensitive
• Use ILIKE (PostgreSQL) or LOWER() for case-insensitive:

WHERE LOWER(name) LIKE 'john%';

🎮 Putting It All Together

The Ultimate Query

SELECT name, price, category
FROM products
WHERE category IN ('Electronics', 'Toys')
  AND price BETWEEN 10 AND 100
  AND name LIKE '%Pro%'
  AND NOT discontinued = true;

Translation: “Find products that are Electronics or Toys, priced $10-$100, have ‘Pro’ in the name, and are NOT discontinued!”

🏆 Quick Reference Card

🚀 You Did It!

You now know how to:

• ✅ Filter data with WHERE
• ✅ Compare using operators (=, >, <, etc.)
• ✅ Combine conditions (AND, OR, NOT)
• ✅ Understand precedence (parentheses first!)
• ✅ Use BETWEEN for ranges
• ✅ Use IN for lists
• ✅ Search with LIKE and wildcards

You’re no longer searching through millions of rows—you’re finding exactly what you need, every time! 🎯