Forum

"How to Use SQL CAS...
 
Share:
Notifications
Clear all

"How to Use SQL CASE Statement for Conditional Logic"


Posts: 89
Admin
Topic starter
(@sql-admin)
Estimable Member
Joined: 4 years ago

Introduction
The SQL CASE statement is a powerful conditional expression that allows you to add logic directly into your queries. With CASE, you can implement IF-THEN-ELSE functionality in your SQL statements. In this post, we’ll explore how to use the CASE statement effectively with real-world examples and practical use cases.


What is the SQL CASE Statement?

The CASE statement is used to evaluate conditions and return specific results based on those conditions. It comes in two forms:

  1. Simple CASE: Compares a column or expression to a set of values.
  2. Searched CASE: Evaluates a series of Boolean expressions.

Syntax:

Simple CASE:

sql
 
CASE column_name
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE resultN
END;

Searched CASE:

sql
 
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE resultN
END;

1. Simple CASE Example

Consider a Products table:

ProductID ProductName CategoryID
1 Laptop 1
2 Phone 2
3 Tablet 1

Query: Categorizing Products

sql
 
SELECT ProductName,
CASE CategoryID
WHEN 1 THEN 'Electronics'
WHEN 2 THEN 'Mobiles'
ELSE 'Other'
END AS Category
FROM Products;

Output:

ProductName Category
Laptop Electronics
Phone Mobiles
Tablet Electronics

2. Searched CASE Example

Let’s add logic based on price ranges in a Sales table:

SaleID ProductName Price
101 Laptop 1000
102 Phone 500
103 Tablet 300

Query: Price Categorization

sql
 
SELECT ProductName,
CASE
WHEN Price > 800 THEN 'High'
WHEN Price BETWEEN 400 AND 800 THEN 'Medium'
ELSE 'Low'
END AS PriceCategory
FROM Sales;

Output:

ProductName PriceCategory
Laptop High
Phone Medium
Tablet Low

3. Using CASE in WHERE Clause

You can use the CASE statement within a WHERE clause to apply conditional filtering.

Query: Filter Products Based on Dynamic Conditions

sql
 
SELECT *
FROM Sales
WHERE 1 = CASE
WHEN Price > 800 THEN 1
ELSE 0
END;

Output:

SaleID ProductName Price
101 Laptop 1000

4. Using CASE in ORDER BY Clause

You can use the CASE statement in the ORDER BY clause to sort data conditionally.

Query: Sorting Products by Price Category

sql
 
SELECT ProductName, Price
FROM Sales
ORDER BY CASE
WHEN Price > 800 THEN 1
WHEN Price BETWEEN 400 AND 800 THEN 2
ELSE 3
END;

5. Aggregating Data with CASE

The CASE statement can be combined with aggregate functions like SUM or COUNT to conditionally summarize data.

Query: Calculate Revenue by Product Category

sql
 
SELECT
SUM(CASE WHEN Price > 800 THEN Price ELSE 0 END) AS HighRevenue,
SUM(CASE WHEN Price BETWEEN 400 AND 800 THEN Price ELSE 0 END) AS MediumRevenue,
SUM(CASE WHEN Price < 400 THEN Price ELSE 0 END) AS LowRevenue
FROM Sales;

Output:

HighRevenue MediumRevenue LowRevenue
1000 500 300

6. Dynamic Columns with CASE

You can use CASE to dynamically generate columns based on conditions.

Query: Display Discounted Prices

sql
 
SELECT ProductName,
Price,
CASE
WHEN Price > 800 THEN Price * 0.9
WHEN Price BETWEEN 400 AND 800 THEN Price * 0.95
ELSE Price
END AS DiscountedPrice
FROM Sales;

Output:

ProductName Price DiscountedPrice
Laptop 1000 900
Phone 500 475
Tablet 300 300

Best Practices for Using CASE in SQL

  1. Readability: Break complex conditions into smaller, clear WHEN clauses.
  2. Optimization: Minimize the number of conditions for better performance.
  3. Error Handling: Always include an ELSE clause to handle unexpected values.

Conclusion
The SQL CASE statement is a versatile tool for conditional logic in your queries, from data transformation to filtering and aggregation. Start using CASE to make your queries more dynamic and flexible. For more SQL queries and tips, visit our SQL forum.

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Share: