SQL
Apply Conditional Logic with SQL CASE Statements
Implement conditional logic with SQL CASE statements to categorize data or customize sorting rules directly within your SELECT or ORDER BY clauses.
SELECT
product_name,
price,
CASE
WHEN price < 10.00 THEN 'Budget'
WHEN price >= 10.00 AND price < 50.00 THEN 'Mid-Range'
ELSE 'Premium'
END AS price_category,
CASE
WHEN stock_quantity = 0 THEN 'Out of Stock'
WHEN stock_quantity < 10 THEN 'Low Stock'
ELSE 'In Stock'
END AS stock_status
FROM products
ORDER BY
CASE
WHEN stock_status = 'Out of Stock' THEN 1
WHEN stock_status = 'Low Stock' THEN 2
ELSE 3
END,
price DESC;
How it works: This snippet showcases the versatility of SQL CASE statements for applying conditional logic. It categorizes products into 'Budget', 'Mid-Range', or 'Premium' based on their price, and assigns a 'stock_status' based on stock_quantity. Furthermore, it demonstrates how CASE can be used within an ORDER BY clause to define custom sorting logic, prioritizing 'Out of Stock' items first.