SQL Case Statement

Read this article to understand the SQL Case Statement along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions on case.

In this post, we will explore the power of the SQL CASE statement and its versatile use in SQL queries. We will learn how to add conditional logic to your queries, enabling you to perform different actions or return specific values based on given conditions. We will discover how to transform data, perform conditional aggregations, customise sorting rules, and apply filters using the SQL CASE statement.

First, let’s talk about the theory of Case and why we should use it.

Definition

The SQL CASE keyword provides conditional logic in SQL queries or statements. It allows you to perform different actions or return different values based on specified conditions. The purpose of using the SQL CASE keyword is to add flexibility and control to your queries by evaluating conditions and determining the appropriate course of action based on those conditions.

The CASE keyword is typically in conjunction with the WHEN-THEN clauses to define multiple conditions and corresponding actions. It can be used in SELECT statements, UPDATE statements, WHERE clauses, and other parts of a SQL query where conditional logic is required.

Reasons to Use

Below are the some key areas where we need to use Case keyword.

1. Transforming Data: We can use CASE to manipulate or transform data based on specific conditions. For example, we can convert numerical values to categories or display custom labels based on certain criteria.
2. Conditional Aggregation: You can use CASE within aggregate functions like SUM, COUNT, or AVG to conditionally include or exclude certain rows from the calculation.
3. Sorting and Ordering: You can use CASE in ORDER BY clauses to define custom sorting rules based on specific conditions.
4. Filtering and Data Selection: You can use CASE in WHERE clauses to filter rows based on different conditions.

Syntax

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

• The CASE keyword marks the beginning of the CASE statement.
• Each WHEN clause specifies a condition to evaluate.
• The THEN keyword is used to indicate the result or value to be returned if the corresponding condition is true.
• You can have multiple WHEN clauses to define different conditions and results.
• The ELSE clause is optional and specifies a default result to be returned when no conditions are true.
• The END keyword marks the end of the CASE statement.

Example 1

Let’s understood the below syntax and understood the example

SELECT column1, column2,
CASE column3
WHEN 'value1' THEN 'Result A'
WHEN 'value2' THEN 'Result B'
ELSE 'Result C'
END AS 'Result'
FROM your_table;

In this example, the CASE statement is used to transform the values of column3 into corresponding results. If column3 matches ‘value1’, it returns ‘Result A’, if it matches ‘value2’, it returns ‘Result B’, and for any other value, it returns ‘Result C’.

Example 2

SELECT column1, column2,
CASE
WHEN condition1 THEN 'Result X'
WHEN condition2 THEN 'Result Y'
ELSE 'Result Z'
END AS 'Result'
FROM your_table;

In this example, the CASE statement evaluates conditions directly without comparing them to a specific column. Depending on the conditions, it returns different results. For example, if condition1 is true, it returns ‘Result X’, if condition2 is true, it returns ‘Result Y’, and otherwise, it returns ‘Result Z’.

I hope all above example clears the concept of Case and it’s use.

Linking

As discussed, Case keyword is used to provide conditional logic in SQL queries or statements. We can use aggregate functions within CASE, and combine it with other SQL clauses to create more complex logic. The versatility of the CASE statement allows you to perform conditional transformations, calculations, and result customization within your SQL queries.

Interview Questions

Basic Case keyword SQL Interview questions

1. What do we use the SQL CASE statement for?
2. What is the syntax of a simple CASE statement?
3. How does a simple CASE statement differ from a searched CASE statement?
4. Can you have multiple WHEN clauses in a SQL CASE statement?
5. What is the purpose of the ELSE clause in a SQL CASE statement?
6. Can you use the SQL CASE statement in conjunction with other SQL clauses like WHERE or ORDER BY?
7. How can you use the SQL CASE statement to transform or manipulate data in a query?
8. Is it possible to nest CASE statements within each other?
9. Can you use the SQL CASE statement with aggregate functions? If so, how?
10. How does the SQL CASE statement handle NULL values?

Intermediate Case keyword SQL Interview questions

1. How can you use the SQL CASE statement to perform conditional aggregations in a query? Provide an example.
2. How does the SQL CASE statement handle multiple conditions within a single WHEN clause? Can you provide an example?
3. How can you use the SQL CASE statement to create calculated or derived columns in a query result set? Provide an example.

Advanced Case keyword SQL Interview questions

1. Can you use the SQL CASE statement in the GROUP BY clause of a query? If so, how does it affect the grouping and aggregation process?

Quiz

Click on this link to solve few MCQ questions on Case Keyword.

Happy offline learning… Lets meet in next session. Follow this link for more content.