Using CASE Statement in SQL
The CASE statement is a powerful tool in SQL that allows you to make conditional statements based on predefined criteria. It allows you to change the outcome of a query based on specific conditions, and it can be used in various clauses like SELECT, WHERE, ORDER BY, and HAVING.
1) Using CASE in SELECT
The SELECT statement is the most commonly used SQL statement, and it is used to retrieve information from a database. The CASE statement can be used in the SELECT clause to modify the output of a query based on specific conditions.
Let’s take an example to better illustrate it:
SELECT
ProductName,
UnitPrice,
CASE
WHEN UnitPrice < 20 THEN 'Cheap'
WHEN UnitPrice >= 20 AND UnitPrice < 50 THEN 'Moderately Priced'
ELSE 'Expensive'
END AS PriceCategory
FROM
Products
In the above example, we are selecting three columns from the Products table. The third column uses the CASE statement to assign each product to a price category based on its unit price.
This statement will show the name of the product, its unit price, and the price category.
Explanation of CASE statement and its syntax
The syntax for the CASE statement is as follows:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
The CASE statement evaluates each condition in order and returns the result associated with the first condition that evaluates to true. If none of the conditions are true, the statement returns the default result.
2) Other uses of CASE statement
Apart from using the CASE statement in the SELECT clause, it can be used in other SQL clauses like WHERE, ORDER BY, and HAVING.
Using CASE in WHERE clause
In the WHERE clause, you can use the CASE statement to filter the data based on specific criteria. For example:
SELECT *
FROM
Customers
WHERE
CASE
WHEN Country = 'USA' THEN 'North America'
WHEN Country = 'Canada' THEN 'North America'
WHEN Country = 'Mexico' THEN 'North America'
WHEN Country = 'Brazil' THEN 'South America'
WHEN Country = 'Argentina' THEN 'South America'
ELSE 'Other'
END = 'North America'
In the above example, we have used the CASE statement in the WHERE clause to filter the customers based on their country. We have assigned customers from the USA, Canada, and Mexico to the North America category, and customers from Brazil and Argentina to the South America category.
All other customers are assigned to the Other category.
Using CASE to sort data according to multiple criteria
The CASE statement can also be used in the ORDER BY clause to sort data according to multiple criteria. For example:
SELECT *
FROM
Products
ORDER BY
CASE
WHEN Discontinued = 1 THEN 1
ELSE 0
END,
UnitPrice DESC
In the above example, we have used the CASE statement to sort the products in descending order of their unit price, with discontinued products appearing first. The CASE statement assigns a value of 1 to discontinued products and 0 to the others, which determines their placement in the results.
3) Using CASE in ORDER BY clause
The ORDER BY clause is typically used to sort query results in ascending or descending order, but with the CASE statement, you can sort your results based on customized criteria. When you introduce the CASE statement in ORDER BY, it enhances sorting results significantly.
Practical example of using CASE in ORDER BY
Let’s say you have a table of employees, and you want to sort them by their position and salary. However, you want to prioritize the employees’ positions over their salary and sort them accordingly.
You can achieve this by using the CASE statement in the ORDER BY clause as follows:
SELECT EmployeeName, Position, Salary
FROM Employees
ORDER BY
CASE
WHEN Position = 'Manager' THEN 1
WHEN Position = 'Supervisor' THEN 2
WHEN Position = 'Staff' THEN 3
ELSE 4
END,
Salary DESC;
In the above query, we’re sorting the employees based on their position by assigning a unique integer value to each position. If one employee has the same position as another, they’re sorted in descending order based on their salary.
Using DESC keyword with CASE in ORDER BY
You can also use the DESC keyword together with CASE to sort the results in descending order, as shown in the previous example.
4) Other uses of CASE statement
Apart from using the CASE statement in SELECT, WHERE, HAVING, and ORDER BY clauses, it’s also useful in data modifying statements like UPDATE and INSERT, and for conditional summarization.
Using CASE statement with data modifying statements
The CASE statement can also be used with data modifying statements like UPDATE and INSERT. The statement allows you to modify data based on specific criteria, as shown in the following example:
UPDATE Employees
SET Salary = CASE
WHEN Salary < 50000 THEN Salary * 1.1
WHEN Salary >= 50000 AND Salary < 70000 THEN Salary * 1.12
ELSE Salary * 1.15
END;
In the above example, we’re increasing employee salaries based on their current salary band. If an employee’s salary is below 50,000, their salary is increased by 10%.
If their salary is between 50,000 and 70,000, their salary is increased by 12%. And if their salary is above 70,000, the salary is increased by 15%.
Using CASE for conditional summarization
The CASE statement can also be used for conditional summarization, where you want to summarize and group data based on specific criteria. For example, you can use the following query to group employees by their position and count the number of employees in each position:
SELECT Position, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Position
ORDER BY
CASE
WHEN Position = 'Manager' THEN 1
WHEN Position = 'Supervisor' THEN 2
WHEN Position = 'Staff' THEN 3
ELSE 4
END;
In the above query, we’re grouping employees by their position and using the CASE statement to order the results based on their position. This query allows us to see the total number of employees in each position, and we’re ordering the positions in ascending order based on their level of importance.
5) Conclusion and further learning resources
Overview of the main uses of the CASE statement
The CASE statement is a powerful tool in SQL that allows you to make conditional statements based on predefined criteria. It’s a flexible statement that can be used in various SQL clauses like SELECT, WHERE, HAVING, ORDER BY, UPDATE, INSERT, and for conditional summarization.
Below is a summary of the main uses of the CASE statement:
- In SELECT clause: The CASE statement can be used in the SELECT clause to modify the output of a query based on specific conditions.
- In WHERE clause: You can use the CASE statement to filter data based on specific criteria.
- In ORDER BY clause: With the CASE statement, you can sort query results based on customized criteria using ORDER BY.
- In UPDATE and INSERT statements: You can use the CASE statement with data modifying statements like UPDATE and INSERT.
- For conditional summarization: The CASE statement can be used to group data based on specific criteria.
Recommendation of Standard SQL Functions course for further learning
To become proficient in SQL, it’s essential to master its functions and statements. One of the best ways to do this is by taking online courses designed for SQL beginners and experts.
One such course is the Standard SQL Functions available on the Google Cloud Platform. The Standard SQL Functions course covers the fundamental concepts of SQL, including the CASE statement, and its uses in queries.
The course includes practical examples and exercises on SELECT, WHERE, HAVING, ORDER BY, UPDATE, and INSERT statements to help you build your SQL skills. The course is designed for those who have some experience with SQL and want to expand their knowledge and skills further.
It’s free, and you can complete the course at your own pace. The course is also interactive, with numerous exercises throughout the lessons, and quizzes at the end of each module to help reinforce your understanding of the material.
The Standard SQL Functions course is an excellent resource for those who want to improve their SQL abilities and gain practical experience that is useful in real-world scenarios. With the CASE statement being such an essential part of SQL queries, mastering its use can open up a world of possibilities in data management and analysis.
In conclusion, the CASE statement is a powerful tool in SQL that allows you to make conditional statements based on predefined criteria. It’s a flexible statement that can be used in various SQL clauses.
While the article has focused on the main uses of the CASE statement, there is still a lot to learn. The Standard SQL Functions course offered by Google Cloud Platform is an excellent resource for furthering your SQL skills.
With the right resources, you can become proficient in SQL and make the most of its powerful functions and statements. In conclusion, the CASE statement is a versatile tool in SQL that allows you to make conditional statements based on predefined criteria.
Its main uses include modifying output in the SELECT clause, filtering data in the WHERE clause, sorting query results in the ORDER BY clause, modifying data in UPDATE and INSERT statements, and performing conditional summarization. Mastery of the CASE statement is essential for efficient data management in SQL.
Besides, for those seeking to expand their SQL skills, the Google Cloud Platform offers the Standard SQL Functions course to improve their knowledge. The course covers essential concepts, including the CASE statement, and provides practical examples to refine your SQL abilities.
Utilizing the CASE statement, SQL users can refine their data management skills and streamline their analyses, providing a competitive edge in data-driven environments.