22. MySQL: CASE Statement
3 min read
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.
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.
CASE WHEN condition_1 THEN output_1 WHEN condition_2 THEN output_2 WHEN condition_N THEN output_N ELSE result END;
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;
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;
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;
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.