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.

1. CASE (If-Else): "IF a passenger has flown > 50 times, mark them as 'VIP'. ELSE, mark them as 'Regular'." ✅
2. 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

---

📊 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 1st 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."

---

📈 Learning Path

• ← Back: NULL Handling
• Next: Set Operators (UNION/EXCEPT) →