Skip to content

SQL Decision Logic (CASE & EXISTS) 🧠

Prerequisites: Logical Operators, Subqueries

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

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 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