CASE Expression

In the previous lesson, you learned about:

text exampleWoHoTech

PIVOT
UNPIVOT

which are used for transforming data between rows and columns.

Now we move to one of the most commonly used SQL features:

CASE Expression

CASE allows SQL queries to make decisions based on conditions.

Think of CASE as SQL's version of:

text exampleWoHoTech

IF-ELSE
SWITCH
CONDITIONAL LOGIC

CASE is heavily used in:

text exampleWoHoTech

Reporting
Analytics
Dashboards
Data Transformation
Business Rules

and is one of the most frequently asked SQL interview topics.

What is CASE Expression?

The CASE Expression allows SQL to evaluate conditions and return different values based on those conditions.

In simple words:

text exampleWoHoTech

IF Condition True
      ↓
Return Value A

ELSE
      ↓
Return Value B

Simple Definition

CASE is a conditional expression that returns different results based on specified conditions.

Why is CASE Needed?

Suppose we have:

Employee	Salary
Rahul	40000
Priya	70000
Amit	90000

Requirement:

text exampleWoHoTech

Salary < 50000
Low Salary

Salary >= 50000
High Salary

CASE can generate this classification automatically.

Types of CASE Expression

SQL supports:

text exampleWoHoTech

Simple CASE
Searched CASE

Simple CASE

Compares one expression against multiple values.

Syntax

CASE Expression WHEN Value1 THEN Result1 WHEN Value2 THEN Result2 ELSE DefaultResult END

Example

SELECT EmployeeName, CASE DepartmentID WHEN 1 THEN 'HR' WHEN 2 THEN 'IT' WHEN 3 THEN 'Finance' ELSE 'Unknown' END AS DepartmentName FROM Employees;

Output

Employee	Department
Rahul	HR
Priya	IT

Understanding Simple CASE

Evaluation:

text exampleWoHoTech

DepartmentID = 1
        ↓
HR

DepartmentID = 2
        ↓
IT

DepartmentID = 3
        ↓
Finance

Searched CASE

More powerful.

Uses conditions.

Syntax

CASE WHEN Condition1 THEN Result1 WHEN Condition2 THEN Result2 ELSE Result END

Example

SELECT EmployeeName, Salary, CASE WHEN Salary < 50000 THEN 'Low Salary' WHEN Salary < 80000 THEN 'Medium Salary' ELSE 'High Salary' END AS SalaryCategory FROM Employees;

Output

Employee	Salary	Category
Rahul	40000	Low Salary
Priya	70000	Medium Salary
Amit	90000	High Salary

How CASE Works

Execution:

text exampleWoHoTech

Condition 1
      ↓
True?
      ↓
Return Result

False?
      ↓
Check Next Condition

ELSE Clause

The ELSE block handles unmatched conditions.

Example:

CASE WHEN Salary > 50000 THEN 'High' ELSE 'Low' END

Without ELSE

CASE WHEN Salary > 50000 THEN 'High' END

Unmatched rows return:

text exampleWoHoTech

NULL

CASE in SELECT

Most common usage.

Example:

SELECT EmployeeName, CASE WHEN Salary >= 80000 THEN 'Excellent' WHEN Salary >= 60000 THEN 'Good' ELSE 'Average' END AS Performance FROM Employees;

CASE in ORDER BY

Custom sorting.

Example:

SELECT * FROM Employees ORDER BY CASE WHEN Department='HR' THEN 1 WHEN Department='IT' THEN 2 ELSE 3 END;

CASE in WHERE Clause

Example:

SELECT * FROM Employees WHERE CASE WHEN Salary > 50000 THEN 1 ELSE 0 END = 1;

CASE in UPDATE

Example:

UPDATE Employees SET Bonus = CASE WHEN Salary > 80000 THEN 10000 WHEN Salary > 50000 THEN 5000 ELSE 2000 END;

Output

Bonus assigned automatically.

CASE in Aggregate Functions

Very common interview topic.

Count Employees by Category

SELECT SUM ( CASE WHEN Salary > 50000 THEN 1 ELSE 0 END ) AS HighSalaryEmployees FROM Employees;

Example Data

Salary
---------
40000
70000
90000

Result:

text exampleWoHoTech

2

Conditional Aggregation

Example:

SELECT SUM ( CASE WHEN Department='IT' THEN Salary ELSE 0 END ) AS TotalITSalary FROM Employees;

Student Grade Example

Marks Table:

Student	Marks
Rahul	95
Priya	80
Amit	60

Query

SELECT StudentName, CASE WHEN Marks >= 90 THEN 'A' WHEN Marks >= 75 THEN 'B' WHEN Marks >= 60 THEN 'C' ELSE 'F' END AS Grade FROM Students;

Output

Student	Grade
Rahul	A
Priya	B
Amit	C

Banking Example

Requirement:

text exampleWoHoTech

Classify Accounts

CASE WHEN Balance >= 100000 THEN 'Premium' WHEN Balance >= 50000 THEN 'Standard' ELSE 'Basic' END

E-Commerce Example

Requirement:

text exampleWoHoTech

Order Status

CASE WHEN Status = 1 THEN 'Pending' WHEN Status = 2 THEN 'Shipped' WHEN Status = 3 THEN 'Delivered' END

Payroll Example

Requirement:

text exampleWoHoTech

Tax Slabs

CASE WHEN Salary > 100000 THEN Salary * 0.20 WHEN Salary > 50000 THEN Salary * 0.10 ELSE Salary * 0.05 END

University Example

Requirement:

text exampleWoHoTech

Attendance Status

CASE WHEN Attendance >= 75 THEN 'Eligible' ELSE 'Not Eligible' END

Nested CASE

CASE inside CASE.

Example:

CASE WHEN Salary > 50000 THEN CASE WHEN Department='IT' THEN 'IT Bonus' ELSE 'Standard Bonus' END ELSE 'No Bonus' END

CASE vs IF

Feature	CASE	IF
SQL Standard	Yes	No
Multiple Conditions	Yes	Limited
Portability	High	Low
Readability	Better	Moderate

Advantages of CASE

Conditional Logic

Primary purpose.

SQL Standard

Supported by almost all databases.

Flexible

Works in SELECT, WHERE, ORDER BY, UPDATE.

Reporting Friendly

Widely used in dashboards.

Supports Aggregations

Conditional calculations become easy.

Disadvantages

Complex Queries

Large CASE blocks become difficult to read.

Performance Impact

Many conditions may increase processing.

Nested CASE Complexity

Harder to maintain.

Common Mistakes

Missing ELSE

May return NULL unexpectedly.

Incorrect Condition Order

Conditions evaluated top to bottom.

Overusing Nested CASE

Reduces readability.

Using CASE Instead of Proper Design

Not always the best solution.

Best Practices

Always Include ELSE

Handle unexpected values.

Keep Conditions Ordered

Most specific conditions first.

Use Meaningful Aliases

Example:

sql exampleWoHoTech

AS SalaryCategory
AS EmployeeGrade

Avoid Deep Nesting

Simplify logic when possible.

Test Edge Cases

Verify all conditions.

Common Interview Questions

What is CASE Expression?

A conditional SQL expression that returns values based on conditions.

What is the difference between Simple CASE and Searched CASE?

Simple CASE compares values.

Searched CASE evaluates conditions.

Can CASE be used in WHERE?

Yes.

Can CASE be used with aggregate functions?

Yes.

Very commonly.

What happens if ELSE is omitted?

Unmatched rows return NULL.

Summary

CASE Expression is one of the most important SQL features for implementing conditional logic. It allows queries to classify data, perform conditional calculations, create custom reports, and apply business rules directly within SQL.

In this lesson, you learned:

What CASE Expression is
Simple CASE
Searched CASE
CASE in SELECT
CASE in WHERE
CASE in UPDATE
Conditional Aggregation
Real-world examples
Advantages and disadvantages
Best practices

Mastering CASE Expressions is essential because they are used extensively in reporting, analytics, business intelligence, and SQL interviews.

Next Step

Continue to the next lesson:

MERGE Statement →

In the previous lesson, you learned about:

text exampleWoHoTech

PIVOT
UNPIVOT

which are used for transforming data between rows and columns.

Now we move to one of the most commonly used SQL features:

CASE Expression

CASE allows SQL queries to make decisions based on conditions.

Think of CASE as SQL's version of:

text exampleWoHoTech

IF-ELSE
SWITCH
CONDITIONAL LOGIC

CASE is heavily used in:

text exampleWoHoTech

Reporting
Analytics
Dashboards
Data Transformation
Business Rules

and is one of the most frequently asked SQL interview topics.

What is CASE Expression?

The CASE Expression allows SQL to evaluate conditions and return different values based on those conditions.

In simple words:

text exampleWoHoTech

IF Condition True
      ↓
Return Value A

ELSE
      ↓
Return Value B

Simple Definition

CASE is a conditional expression that returns different results based on specified conditions.

Why is CASE Needed?

Suppose we have:

Employee	Salary
Rahul	40000
Priya	70000
Amit	90000

Requirement:

text exampleWoHoTech

Salary < 50000
Low Salary

Salary >= 50000
High Salary

CASE can generate this classification automatically.

Types of CASE Expression

SQL supports:

text exampleWoHoTech

Simple CASE
Searched CASE

Simple CASE

Compares one expression against multiple values.

Syntax

CASE Expression WHEN Value1 THEN Result1 WHEN Value2 THEN Result2 ELSE DefaultResult END

Example

SELECT EmployeeName, CASE DepartmentID WHEN 1 THEN 'HR' WHEN 2 THEN 'IT' WHEN 3 THEN 'Finance' ELSE 'Unknown' END AS DepartmentName FROM Employees;

Output

Employee	Department
Rahul	HR
Priya	IT

Understanding Simple CASE

Evaluation:

text exampleWoHoTech

DepartmentID = 1
        ↓
HR

DepartmentID = 2
        ↓
IT

DepartmentID = 3
        ↓
Finance

Searched CASE

More powerful.

Uses conditions.

Syntax

CASE WHEN Condition1 THEN Result1 WHEN Condition2 THEN Result2 ELSE Result END

Example

SELECT EmployeeName, Salary, CASE WHEN Salary < 50000 THEN 'Low Salary' WHEN Salary < 80000 THEN 'Medium Salary' ELSE 'High Salary' END AS SalaryCategory FROM Employees;

Output

Employee	Salary	Category
Rahul	40000	Low Salary
Priya	70000	Medium Salary
Amit	90000	High Salary

How CASE Works

Execution:

text exampleWoHoTech

Condition 1
      ↓
True?
      ↓
Return Result

False?
      ↓
Check Next Condition

ELSE Clause

The ELSE block handles unmatched conditions.

Example:

CASE WHEN Salary > 50000 THEN 'High' ELSE 'Low' END

Without ELSE

CASE WHEN Salary > 50000 THEN 'High' END

Unmatched rows return:

text exampleWoHoTech

NULL

CASE in SELECT

Most common usage.

Example:

SELECT EmployeeName, CASE WHEN Salary >= 80000 THEN 'Excellent' WHEN Salary >= 60000 THEN 'Good' ELSE 'Average' END AS Performance FROM Employees;

CASE in ORDER BY

Custom sorting.

Example:

SELECT * FROM Employees ORDER BY CASE WHEN Department='HR' THEN 1 WHEN Department='IT' THEN 2 ELSE 3 END;

CASE in WHERE Clause

Example:

SELECT * FROM Employees WHERE CASE WHEN Salary > 50000 THEN 1 ELSE 0 END = 1;

CASE in UPDATE

Example:

UPDATE Employees SET Bonus = CASE WHEN Salary > 80000 THEN 10000 WHEN Salary > 50000 THEN 5000 ELSE 2000 END;

Output

Bonus assigned automatically.

CASE in Aggregate Functions

Very common interview topic.

Count Employees by Category

SELECT SUM ( CASE WHEN Salary > 50000 THEN 1 ELSE 0 END ) AS HighSalaryEmployees FROM Employees;

Example Data

Salary
---------
40000
70000
90000

Result:

text exampleWoHoTech

2

Conditional Aggregation

Example:

SELECT SUM ( CASE WHEN Department='IT' THEN Salary ELSE 0 END ) AS TotalITSalary FROM Employees;

Student Grade Example

Marks Table:

Student	Marks
Rahul	95
Priya	80
Amit	60

Query

SELECT StudentName, CASE WHEN Marks >= 90 THEN 'A' WHEN Marks >= 75 THEN 'B' WHEN Marks >= 60 THEN 'C' ELSE 'F' END AS Grade FROM Students;

Output

Student	Grade
Rahul	A
Priya	B
Amit	C

Banking Example

Requirement:

text exampleWoHoTech

Classify Accounts

CASE WHEN Balance >= 100000 THEN 'Premium' WHEN Balance >= 50000 THEN 'Standard' ELSE 'Basic' END

E-Commerce Example

Requirement:

text exampleWoHoTech

Order Status

CASE WHEN Status = 1 THEN 'Pending' WHEN Status = 2 THEN 'Shipped' WHEN Status = 3 THEN 'Delivered' END

Payroll Example

Requirement:

text exampleWoHoTech

Tax Slabs

CASE WHEN Salary > 100000 THEN Salary * 0.20 WHEN Salary > 50000 THEN Salary * 0.10 ELSE Salary * 0.05 END

University Example

Requirement:

text exampleWoHoTech

Attendance Status

CASE WHEN Attendance >= 75 THEN 'Eligible' ELSE 'Not Eligible' END

Nested CASE

CASE inside CASE.

Example:

CASE WHEN Salary > 50000 THEN CASE WHEN Department='IT' THEN 'IT Bonus' ELSE 'Standard Bonus' END ELSE 'No Bonus' END

CASE vs IF

Feature	CASE	IF
SQL Standard	Yes	No
Multiple Conditions	Yes	Limited
Portability	High	Low
Readability	Better	Moderate

Advantages of CASE

Conditional Logic

Primary purpose.

SQL Standard

Supported by almost all databases.

Flexible

Works in SELECT, WHERE, ORDER BY, UPDATE.

Reporting Friendly

Widely used in dashboards.

Supports Aggregations

Conditional calculations become easy.

Disadvantages

Complex Queries

Large CASE blocks become difficult to read.

Performance Impact

Many conditions may increase processing.

Nested CASE Complexity

Harder to maintain.

Common Mistakes

Missing ELSE

May return NULL unexpectedly.

Incorrect Condition Order

Conditions evaluated top to bottom.

Overusing Nested CASE

Reduces readability.

Using CASE Instead of Proper Design

Not always the best solution.

Best Practices

Always Include ELSE

Handle unexpected values.

Keep Conditions Ordered

Most specific conditions first.

Use Meaningful Aliases

Example:

sql exampleWoHoTech

AS SalaryCategory
AS EmployeeGrade

Avoid Deep Nesting

Simplify logic when possible.

Test Edge Cases

Verify all conditions.

Common Interview Questions

What is CASE Expression?

A conditional SQL expression that returns values based on conditions.

What is the difference between Simple CASE and Searched CASE?

Simple CASE compares values.

Searched CASE evaluates conditions.

Can CASE be used in WHERE?

Yes.

Can CASE be used with aggregate functions?

Yes.

Very commonly.

What happens if ELSE is omitted?

Unmatched rows return NULL.

Summary

In this lesson, you learned:

What CASE Expression is
Simple CASE
Searched CASE
CASE in SELECT
CASE in WHERE
CASE in UPDATE
Conditional Aggregation
Real-world examples
Advantages and disadvantages
Best practices

Mastering CASE Expressions is essential because they are used extensively in reporting, analytics, business intelligence, and SQL interviews.

Next Step

Continue to the next lesson:

MERGE Statement →

What is CASE Expression?

Simple Definition

Why is CASE Needed?

Types of CASE Expression

Simple CASE

Syntax

Example

Output

Understanding Simple CASE

Searched CASE

Syntax

Example

Output

How CASE Works

ELSE Clause

Without ELSE

CASE in SELECT

CASE in ORDER BY

CASE in WHERE Clause

CASE in UPDATE

Output

CASE in Aggregate Functions

Count Employees by Category

Example Data

Conditional Aggregation

Student Grade Example

Query

Output

Banking Example

E-Commerce Example

Payroll Example

University Example

Nested CASE

CASE vs IF

Advantages of CASE

Conditional Logic

SQL Standard

Flexible

Reporting Friendly

Supports Aggregations

Disadvantages

Complex Queries

Performance Impact

Nested CASE Complexity

Common Mistakes

Missing ELSE

Incorrect Condition Order

Overusing Nested CASE

Using CASE Instead of Proper Design

Best Practices

Always Include ELSE

Keep Conditions Ordered

Use Meaningful Aliases

Avoid Deep Nesting

Test Edge Cases

Common Interview Questions

What is CASE Expression?

What is the difference between Simple CASE and Searched CASE?

Can CASE be used in WHERE?

Can CASE be used with aggregate functions?

What happens if ELSE is omitted?

Summary

Next Step

Continue learning this concept

What is CASE Expression?

Simple Definition

Why is CASE Needed?

Types of CASE Expression

Simple CASE

Syntax

Example

Output

Understanding Simple CASE

Searched CASE

Syntax

Example

Output

How CASE Works

ELSE Clause

Without ELSE