Nested Case Statement

 A nested CASE is when one CASE expression is placed inside another. This allows for more complex conditional logic, such as multi-level decisions or prioritizing conditions.

SELECT emp_id, first_name, salary, dept_id,
  CASE 
    WHEN salary > 10000 THEN 
      CASE 
        WHEN dept_id = 10 THEN 'Executive'
        WHEN dept_id = 20 THEN 'Senior Manager'
        ELSE 'High Grade'
      END
    WHEN salary > 5000 THEN 'Mid Grade'
    ELSE 'Low Grade'
  END AS employee_grade
FROM emp1;

✅ Explanation:

• If salary > 10,000:

  • Then check dept: if 10 → Executive, if 20 → Senior Manager

• If salary > 5,000 → Mid Grade

• Otherwise → Low Grade

==========================================================================

Example 2: Nested CASE in ORDER BY for Custom Sorting

SELECT emp_id, first_name, salary FROM emp1

ORDER BY CASE WHEN salary < 5000 THEN

      CASE 

        WHEN first_name LIKE 'J%' THEN 1

        ELSE 2

      END

    ELSE 3

  END;

Sort order:

1. High salary + names starting with 'A'

2. High salary + other names

3. Others

==========================================================================

Example 3: Use in UPDATE Statement

UPDATE emp1

SET salary = 

  CASE 

    WHEN dept_id = 10 THEN 

      CASE 

        WHEN salary >=500 THEN 1000

        ELSE 500

      END

    ELSE 0

  END;

  

  select * from emp1 where dept_id=10;

Bonus logic:

• Dept 10 → bonus based on salary

• Other depts → 0