Forum

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

"How to Use SQL CASE Statement for Conditional Logic"

 
Interview Questions & Answers
Last Post by SQL Admin 1 year ago
1 Posts
1 Users
0 Reactions
726 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Jan 13, 2025 1:10 pm
(@sql-admin)
Reputable Member
Joined: 6 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:

ProductIDProductNameCategoryID
1Laptop1
2Phone2
3Tablet1

Query: Categorizing Products

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

Output:

ProductNameCategory
LaptopElectronics
PhoneMobiles
TabletElectronics

2. Searched CASE Example

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

SaleIDProductNamePrice
101Laptop1000
102Phone500
103Tablet300

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:

ProductNamePriceCategory
LaptopHigh
PhoneMedium
TabletLow

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:

SaleIDProductNamePrice
101Laptop1000

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:

HighRevenueMediumRevenueLowRevenue
1000500300

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:

ProductNamePriceDiscountedPrice
Laptop1000900
Phone500475
Tablet300300

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.


Topic Tags
SQL CASE statement exampl conditional logic in SQL SQL queries with CASE advanced SQL queries CASE in WHERE clause

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  SQL CASE statement exampl (1) , conditional logic in SQL (2) , SQL queries with CASE (1) , advanced SQL queries (1) , CASE in WHERE clause (1) ,
Share: