SQL Decision Logic (CASE & EXISTS) 🧠¶

Mentor's Note: SQL isn't just for fetching data; it's for making decisions. The CASE statement is your SQL "If-Else", and EXISTS is the most efficient way to ask, "Is there someone in the other room?" 💡

🌟 The Scenario: The VIP Upgrade 💎¶

Imagine you are managing an airline database.

CASE (If-Else): "IF a passenger has flown > 50 times, mark them as 'VIP'. ELSE, mark them as 'Regular'." ✅
EXISTS (The Check): "Only show flights that EXIST in the boarding gate schedule." ✅

💻 1. The CASE Statement (The Decision Maker)¶

You can use CASE inside a SELECT statement to create custom labels based on data.

-- Scenario: Categorize salaries into levels
SELECT 
    emp_name, 
    salary,
    CASE 
        WHEN salary >= 80000 THEN 'High Earner 💰'
        WHEN salary >= 40000 THEN 'Mid Level ✨'
        ELSE 'Junior 🛒'
    END AS Pay_Grade
FROM employees;

💻 2. The EXISTS Operator (The Presence Check)¶

EXISTS is used to check if a subquery returns any result. It is incredibly fast because it stops looking as soon as it finds one match.

-- Scenario: Find departments that have at least one employee
SELECT dept_name
FROM departments D
WHERE EXISTS (
    SELECT 1 
    FROM employees E 
    WHERE E.dept_id = D.id
);

🎨 Visual Logic: The Flow¶

graph TD
    A[Start Row] --> B{Salary >= 80k?}
    B -- Yes --> C[Label: VIP]
    B -- No --> D{Salary >= 40k?}
    D -- Yes --> E[Label: Mid]
    D -- No --> F[Label: Junior]

📊 Comparison: EXISTS vs IN¶

Which one should you use?

Feature	IN Operator	EXISTS Operator
Best For	Small, hardcoded lists	Large tables/subqueries
Execution	Fetches all IDs first	Stops at 1^st match (Fast!) ⚡
Logic	Checks value equality	Checks if record exists

💡 Pro Tip¶

"In modern databases like Oracle or PostgreSQL, EXISTS is often faster than IN for complex subqueries because it doesn't build a massive list of IDs in memory before checking them."