top of page
hand-businesswoman-touching-hand-artificial-intelligence-meaning-technology-connection-go-

How to use an SQL case statement with an example.

Writing SQL queries with multiple conditions will be a difficult and tiring task.


For example, an if() else if() else {} check case expression handles all SQL conditionals. If the first condition is satisfied, the query stops executing with a return value. The value specified within the else is returned if no condition is satisfied.


So here we will cover:


1. What is an SQL case statement and how it works.

2. An example with the use of SQL Case statement.

3. Some important terms like order by, left join, and alias.


Explanation of SQL Case statement:

In programming, when your requirement consists of a given set of conditions, you end up using conditionals (switch or if-else) to know which block of code to execute when a condition is met.


With SQL, you can do this using the CASE statement. You use the CASE keyword together with the WHEN clause to execute a block of conditional statement code. You use a THEN statement to return the result of the expression. If none of the conditions are met, then you use a final ELSE clause to return a fallback result.


The syntax for SQL Case statement:


CASE

WHEN conditional_statement1 THEN result1

.

.

.

WHEN condition_statementN THEN resultN

ELSE result

END;


When you use the CASE statement, it has to be followed by a WHEN and THEN the result if the first condition is met. If the first condition is not met it keeps on checking the other conditions until the nth (or final) condition. If that is still not met then the ELSE condition gets executed.


Also ELSE part is optional when using the CASE statement. If you are not using the ELSE part, the query returns NULL.


SQL Challenge:

A SQL query to return the employee with the third highest salary from a table. Also, have to replace the position of the DivisionID column with the corresponding DivisionName from the table company_divisions. Then you'll need to replace the ManagerID column with the ManagerName if the ID exists in the table and is not NULL.


What problem does the SQL CASE statement solve in this challenge?


In this challenge, we need the CASE statement to help achieve the followings:

1. Ensure that the MangerID is not NULL.

2. Match the company ManagerID to the company ID and return the Name as the ManagerName.

3. Ensure that if no Name is returned, then the name Numpy Ninja is used as the default ManagerName.

Data you need to solve this challenge: Table 1: company

Id

name

Division_id

Manager_id

salary

356

Valli

100

133

40000

122

Nagesh

101

null

60000

467

Aadya

100

null

80000

112

Avyay

105

467

65000

775

Geeta

103

null

90000

111

Srimati

104

35534

75000

222

JayaShri

102

133

86000

577

Srija

105

12343

76000

133

Poornima

105

577

110000

Table 2: company_divisions

Division_id

Division_name

100

Accounting

101

IT

102

Sales

103

Marketing

104

Engineering

105

Customer Support


How to solve the SQL statement challenge:


Step 1: Get the third-highest salary

First, you'll need to structure a query to return the third-highest salary. You'll do this by selecting from the company table and ordering by salary


You can do that like this:


SELECT * from company ORDER BY salary DESC limit 1 offset 2;


So what’s going on with the above query?

SELECT :

You use the SELECT command with the asterisk (*), to retrieve all columns from the company table.

ORDER BY:

The ORDER BY command orders column(s) in ascending or descending order. SQL orders by ascending (ASC) by default, but we will order the salary column by descending (DESC). This is because we need the desc salary from the highest to the lowest, that is 110,000 - 40,000.

limit:

The limit command limits the number of records returned based on the limit's set value. Since we are only interested in just one row, we will set the limit in the query to 1. This ensures that we will get a return value of a single record every time this query gets executed.

offset:

Offset lets us skip the two highest-paid rows (Poornima and Geeta) and return the third highest-paid (Jayashri).


The query returns the employee's row with the third highest salary, as expected.


​Id

name

Division_id

Manager_id

salary

222

JayaShri

102

133

86000


Step 2: Replace DivisionId with DivisionName:


Now, you need to modify the query by selecting only the columns you need – ID, name, Manager_id, Division_name, and Salary. Then you need to replace the Division_id column with the corresponding Division_name from the table company_divisions.


You can do that like this:


SELECT c."Id" , c."name" , c."Manager_id" , c.salary , cd.division_name

FROM company as c

LEFT JOIN company_divisions as cd on c."Division_id" = cd.division_id

ORDER BY salary DESC limit 1 offset 2;


Here’s the output:

​Id

name

Manager_id

salary

Division_name

222

JayaShri

133

86000

Sales

Let's discuss what's going on in the above query:


LEFT JOIN:

Since records are returned from the left side (company), we will match them using the LEFT JOIN on the right side (company_divisions) using the condition company.Division_id = company_divivsions.id


If a matching record is found, that is the company's id is also present in company_division, then the Division_name column is populated with the actual value from the left join, in our case (Sales). If there is no record, nothing is returned.


as (alias):

The alias used is a temporary name for the table. Using aliases helps improve readability.



Step 3: Replace Manager_id with ManagerName:


We'll use the CASE statement we learned to add conditionals for when the Manager_id is not null and to check if the Manager_id also exists.


The first thing we need to do is check if the company.Manager_iD is not null and make sure that the Id exists in the table. We will apply the CASE statement here.


CASE

WHEN c."Manager_id" IS NOT NULL



The second part of the CASE statement is to replace the Manager_id column with the ManagerName. Then we'll need to use the THEN block we learned earlier like this:


CASE

WHEN c."Manager_id" IS NOT NULL THEN (SELECT c2."name" FROM

company c2 WHERE c2."Id" = c."Manager_id")

ELSE 'Numpy Ninja' END as Manager_name


Finally, we can now include the CASE block into the already existing code we had from STEP 2. This will look somewhat like this now:


SELECT c."Id" , c."name" , c."Manager_id" , c.salary , cd.division_name,

CASE

WHEN c."Manager_id" IS NOT NULL THEN (SELECT c2."name" FROM

company c2 WHERE c2."Id" = c."Manager_id")

ELSE 'Numpy Ninja' END as Manager_name

FROM company as c

LEFT JOIN company_divisions as cd on c."Division_id" = cd.division_id

ORDER BY salary DESC limit 1 offset 2;


The result of Step 3 is the expected output – the employee with the third-highest salary.


Id

name

Division_name

salary

Manager_name

222

JayaShri

Sales

86000

Poornima

We learned about the CASE statement in SQL and how to approach a real-world problem using CASE. We also learned other SQL commands such as SELECT, ORDER BY, LIMIT, OFFSET, LEFT JOIN, and ALIAS.


47 views0 comments

Recent Posts

See All
bottom of page