SQL is a powerful and versatile language for database administration, but sometimes you need to perform conditional logic based on the values of your data. That's where the SQL CASE statement comes in handy. In this article, you'll learn how to use the SQL CASE statement effectively to create dynamic queries, handle null values, and apply custom logic.
The SQL CASE statement is a way of expressing conditional logic in SQL. It allows you to specify different outcomes based on the evaluation of one or more expressions. You can use the SQL CASE statement in various places, such as the SELECT, WHERE, ORDER BY, and GROUP BY clauses. There are two forms of the SQL CASE statement: the simple CASE and the searched CASE.
SQL Case is very useful in adding conditional flexibility to your select statements and return values which otherwise plain select won't return. Example: give hike based on age. if age -25-35 then salary*0.10 age -36-45 then salary*0.20 else salary*0.30 I am considering we are ha select case when case TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) between 25 and 35 then salary*0.10 when case TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) between 36and 45 then salary*0.20 else salary*0.30 as salary_raise end from employees; This will give you the values of salary raise based on the employee age.
The simple CASE statement compares an expression to a set of values and returns a corresponding result. The syntax of the simple CASE statement is:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE resultN
END
For example, you can use the simple CASE statement to categorize products based on their prices:
SELECT product_name, price,
CASE price
WHEN price <= 10 THEN 'Cheap'
WHEN price > 10 AND price <= 20 THEN 'Affordable'
WHEN price > 20 AND price <= 30 THEN 'Moderate'
ELSE 'Expensive'
END AS category
FROM products;
The searched CASE statement evaluates a set of conditions and returns a corresponding result. The syntax of the searched CASE statement is:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END
For example, you can use the searched CASE statement to calculate discounts based on customer types:
SELECT customer_name, customer_type, order_amount,
CASE
WHEN customer_type = 'VIP' THEN order_amount * 0.9
WHEN customer_type = 'Regular' THEN order_amount * 0.95
ELSE order_amount
END AS discounted_amount
FROM orders;
Example: give hike based on age. if age -25-35 then salary*0.10 age -36-45 then salary*0.20 else salary*0.30 I am considering we are ha select case when case TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) between 25 and 35 then salary*0.10 when case TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) between 36and 45 then salary*0.20 else salary*0.30 as salary_raise end from employees;
One of the common challenges in SQL is dealing with null values, which represent missing or unknown data. The SQL CASE statement can be used to address this issue in several ways, such as replacing null values with a default value, excluding null values from calculations or aggregations, or including null values as a separate category or group. For instance, the following SQL code can be used to replace nulls with the default value 'N/A': SELECT product_name, price, CASE WHEN price IS NULL THEN 'N/A' ELSE price END AS price FROM products;
The SQL CASE statement is a powerful tool that can be used to apply custom logic to your data. For example, you can use it to create a new column based on the values of other columns, such as a flag, a score, or a label. Additionally, you can use it to format data according to your preferences, such as changing the case, date format, or currency symbol. Furthermore, you can perform calculations based on conditional logic, such as applying different formulas, rates, or factors. To illustrate this concept, here is an example of using the SQL CASE statement to create a new column based on the values of other columns:
SELECT customer_name, customer_type, order_amount,
CASE
WHEN customer_type = 'VIP' AND order_amount >= 1000 THEN 'High-value'
WHEN customer_type = 'Regular' AND order_amount >= 500 THEN 'Medium-value'
ELSE 'Low-value'
END AS customer_segment
FROM orders;
The SQL CASE statement is a useful tool for database administration, but it also has some limitations and pitfalls. To use the statement effectively, it is important to enclose complex expressions or conditions in parentheses to avoid confusion and errors. The ELSE clause should be used to handle unexpected values or scenarios, to prevent null results or incorrect outcomes. For handling null values, the COALESCE function can be used instead of multiple CASE statements. The IN operator should be used to check for multiple values rather than multiple OR conditions. Lastly, descriptive names should be given to the results or new columns for improved readability and clarity.