SQL CASE Statement

Introduction

In this chapter, we will learn how to use the CASE statement in SQL. The CASE statement is used to provide if-then-else type of logic to SQL queries. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the CASE statement effectively.

What is the CASE Statement?

The CASE statement allows you to perform conditional logic in SQL queries. It returns a value based on specified conditions. The CASE statement can be used in SELECT, INSERT, UPDATE, and DELETE statements.

Syntax for CASE

Basic Syntax

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END

• condition1, condition2, ...: The conditions to be evaluated.
• result1, result2, ...: The results to return when the corresponding condition is true.
• ELSE resultN: The result to return if none of the conditions are true (optional).

Example

Assume we have a table named employees:

SELECT first_name, last_name,
CASE
WHEN department_id = 1 THEN 'HR'
WHEN department_id = 2 THEN 'Finance'
ELSE 'Other'
END AS department_name
FROM employees;

This command retrieves the first_name, last_name, and a calculated department_name based on the department_id.

Step-by-Step Example

1. Create Sample Tables

First, we will create two sample tables named employees and departments.

CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
department_id INT,
salary DECIMAL(10, 2)
);

CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

3. Using the CASE Statement in a SELECT Query

To add a department_name column based on department_id:

SELECT first_name, last_name, email,
CASE
WHEN department_id = 1 THEN 'HR'
WHEN department_id = 2 THEN 'Finance'
ELSE 'Other'
END AS department_name
FROM employees;

Output

first_name	last_name	email	department_name
Ramesh	Kumar	ramesh.kumar@example.com	HR
Sita	Patel	sita.patel@example.com	Finance
Arjun	Singh	arjun.singh@example.com	HR
Priya	Sharma	priyasharma@example.com	Finance
Ramesh	Kumar	ramesh.kumar2@example.com	Other

4. Using CASE with Aggregate Functions

The CASE statement can also be used with aggregate functions to perform conditional aggregation.

Example

To calculate the total salary for each department:

SELECT
CASE
WHEN department_id = 1 THEN 'HR'
WHEN department_id = 2 THEN 'Finance'
ELSE 'Other'
END AS department_name,
SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

Output

department_name	total_salary
HR	105000.00
Finance	122000.00
Other	50000.00

5. Using CASE in an UPDATE Statement

You can use the CASE statement in an UPDATE statement to conditionally update rows.

Example

To give a bonus to employees based on their department:

UPDATE employees
SET salary = salary +
CASE
WHEN department_id = 1 THEN 5000
WHEN department_id = 2 THEN 6000
ELSE 3000
END;

Verify the Update

SELECT first_name, last_name, salary
FROM employees;

Output

first_name	last_name	salary
Ramesh	Kumar	55000.00
Sita	Patel	66000.00
Arjun	Singh	60000.00
Priya	Sharma	68000.00
Ramesh	Kumar	53000.00

6. Using CASE in an INSERT Statement

You can also use the CASE statement in an INSERT statement to conditionally insert rows.

Example

To insert new employees with a conditional department name:

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Anil', 'Mehta', 'anil.mehta@example.com',
CASE
WHEN 'HR' = 'HR' THEN 1
WHEN 'Finance' = 'Finance' THEN 2
ELSE 3
END, 60000);

Verify the Insert

SELECT first_name, last_name, department_id
FROM employees
WHERE email = 'anil.mehta@example.com';

Output

first_name	last_name	department_id
Anil	Mehta	1

Conclusion

The CASE statement is used for adding conditional logic to your SQL queries. This chapter covered the basic syntax, using CASE in SELECT, UPDATE, and INSERT statements, and using CASE with aggregate functions. Understanding how to use the CASE statement effectively will enhance your ability to perform complex data manipulations and analysis.