The case statement in MySQL is a conditional expression that returns a value based on one or more conditions. It is used to determine the result of a query based on different conditions.
Facts to Remember:
The CASE statement in MySQL can be used in various ways, from formatting data to making decisions based on different conditions.
The CASE statement simplifies complex SQL queries by allowing for multiple conditions to be tested in a single statement, making it easier to read and maintain.
The CASE statement can replace the IF statement in SQL, making the code cleaner and more efficient.
By using the CASE statement, the number of nested IF statements can be reduced, which can improve the performance of SQL queries.
The CASE statement in MySQL supports multiple datatypes, including string, numeric, and date/time. This makes it a versatile tool for formatting and manipulating data in SQL.
CASE Statement:
The case statement in MySQL is a conditional expression that returns a value based on one or more conditions.
It is used to determine the result of a query based on different conditions.
For example, you may want to find out how many customers have purchased a particular product, and based on that number, you want to assign them to different categories.
The case statement in MySQL consists of two parts: the expression and the case.
The expression is the condition that you want to evaluate, and the case is the value that will be returned if the condition is true.
Syntax:
CASE
WHEN condition_1 THEN output_1
WHEN condition_2 THEN output_2
WHEN condition_N THEN output_N
ELSE result
END;
Example 1:
Display the film title and their corresponding ratings, but if the rating is 'R' display it as 'Adult Only'.
SELECT
title,
CASE
WHEN rating = 'R' THEN 'Adult Only'
ELSE rating
END AS film_rating
FROM film;
Output:
Example 2:
Display the film title and their corresponding rental rates, but group the films based on their rental rate
SELECT
title,
rental_rate,
CASE
WHEN rental_rate <= 2.99 THEN 'Cheap'
WHEN rental_rate BETWEEN 3.00 AND 4.99 THEN 'Average'
ELSE 'Expensive'
END AS rental_rate_group
FROM film;
Output:
Example 3:
Display the customer name and their corresponding store IDs, but if the store ID is '1', display the store as 'Head Office'.
SELECT
first_name,
last_name,
CASE
WHEN store_id = 1 THEN 'Head Office'
ELSE store_id
END AS store_name
FROM customer;
Output:
In conclusion, the CASE statement in MySQL is a versatile and useful tool for formatting, manipulating, and making decisions based on data in SQL.
With its ability to perform conditional statements, group data, aggregate data, and replace nested IF statements, the CASE statement makes it easier to write complex SQL queries and improve their performance.
Whether you're a seasoned SQL programmer or just starting out, understanding the CASE statement is an important step in becoming proficient with SQL.
Connect with me on LinkedIn: linkedin.com/in/nikhil-shenoy07
To see more examples, you can always refer w3 schools; it has always been my go-to website to understand things.
*****All the outputs provided are a snippet of the actual result.