For an instructor lead, in-depth look at learning SQL click below.
In SQL language, a CASE expression is a conditional statement that allows you to add if-then logic to your SQL queries. CASE expressions provide a way to make decisions and generate different outputs based on those decisions. This increases the overall flexibility and control over your SQL queries.
Basic Syntax of CASE Expressions
The basic syntax of a CASE expressions in SQL is as follows:
|
1 2 3 4 5 6 7 8 |
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE result END |
The CASE statement goes through conditions and returns a value when the first condition is met. Once a valid condition is found, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part then it returns NULL.
Simple CASE Expression
The simplest way to use the SQL CASE expression is to compare an expression to a set of static values. For example, let’s consider that we have a ‘products’ table with two fields: ‘product_id’ and ‘product_name’. Here’s an example of how you could use the CASE statement with this table:
|
1 2 3 4 5 6 7 8 9 |
SELECT product_id, CASE product_name WHEN 'Apple' THEN 'Fruit' WHEN 'Carrot' THEN 'Vegetable' ELSE 'Unknown category' END AS product_category FROM products; |
Searched CASE Expression
A Searched CASE expression allows you to add Boolean logic to a SQL query. It is more elaborate and flexible than the simple CASE expression. Here’s how you can use it:
|
1 2 3 4 5 6 7 8 9 |
SELECT product_id, CASE WHEN product_name LIKE 'A%' THEN 'Name starts with A' WHEN product_name LIKE 'B%' THEN 'Name starts with B' ELSE 'Other' END AS name_description FROM products; |
In conclusion, CASE expressions provide a powerful way to tailor your SQL queries to your specific needs, adding flexibility and control to your data analysis toolkit.