Case in SELECT Statement

sql

prompt-engineering

best-practices

conditional-statements

byAnton Shumikhin·Dec 7, 2024

Flexibly handle conditional logic in SELECT results using a CASE expression:

SELECT 
  column1, 
  CASE 
    WHEN condition THEN 'TrueResult' /*You know it's true...like your devotion to coffee ☕*/
    ELSE 'FalseResult' /*Not Exactly Mission Accomplished 🎯*/
  END AS AliasColumn
FROM 
  table;

Evaluate condition per row. If it rings true, AliasColumn displays 'TrueResult'. If it seems false, we get 'FalseResult'. Always ensure the correct syntax and structure of a CASE for it to work. When juggling with multiple tables, immediately prefix the CASE with a table name.

Practical Explorations

Classifying data with CASE

By changing raw data into distinctive categories, simplifying the interpretation process is a breeze, similar to providing color commentary to a bare play-by-play:

SELECT
  ProductID,
  Price,
  CASE
    WHEN Price < 50 THEN 'Budget' /* Steal deals 🏷️ */
    WHEN Price BETWEEN 50 AND 150 THEN 'Standard' /*Got Milk? Everyone buys this 🥛*/
    WHEN Price > 150 THEN 'Premium' /*Luxury at its finest 🎩*/
    ELSE 'Unknown' /* The Bermuda Triangle of prices 🤷‍♂️ */
  END AS PriceCategory
FROM
  Products;

Custom Sorting with CASE in ORDER BY

Strategically ordering results is achieved by incorporating CASE into your ORDER BY clause. Think of it as quick-sorting your list based on priorities:

SELECT 
  EmployeeID, 
  Name, 
  Salary 
FROM 
  Employees
ORDER BY 
  CASE 
    WHEN Department = 'HR' THEN 1 /* HR is still the boss here 😁 */
    WHEN Department = 'IT' THEN 2 /*Code Ninjas are second 🐱‍💻*/
    ELSE 3
  END;

This might imply HR people are the "overlords," trailed by IT "code-ninjas," and the rest are just mere mortals.

Update with a touch of CASE

Integrate CASE into your UPDATE expressions for conditional assignments. It's a clause having a mid-life crisis, believing it's an IF-THEN block:

UPDATE 
  Employees
SET 
  Role = CASE 
    WHEN PromotionDue THEN 'Senior ' + Role /*We got a Big Kahuna over here 😎 */
    ELSE Role /* Just keep swimming... 🏊‍♂️ */
  END
WHERE 
  EmployeeID = @EmpID;

Exploring beyond the CASE frontiers

Sophisticated Logic with Searched CASE

By allowing complex comparisons, Searched CASE broadens your conditions horizon with AND, OR, and yes, even NOT:

SELECT 
  ProductName, 
  Stock,
  CASE 
    WHEN Stock > 50 AND IsNew THEN 'Fully stocked and new' /* Life's Good 😇 */
    WHEN Stock <= 50 OR IsDiscontinued THEN 'Order soon' /* Just like gas prices...always volatile ⛽️ */
    ELSE 'Available' /* Still in the game ✌️ */
  END AS StockStatus
FROM 
  Inventory;

Reality switch with CASE and LIKE

Create dynamic pattern matching in your queries by merging CASE with LIKE. It's like having a wardrobe for diverse product names:

SELECT 
  ProductName, 
  CASE 
    WHEN ProductName LIKE 'New%' THEN 'New Arrival' /*Fresh off the boat 🚢*/
    WHEN ProductName LIKE 'Old%' THEN 'Classic' /*Wise and Mature, like good wine 🍷*/
    ELSE 'Regular Item' /*Just your Average Joe ⚡*/
  END AS Category
FROM 
  ProductCatalog;

Effective practices and catch-you-off-guard pitfalls

Impeccable Logics

Constructing CASE statements demands distinguishing logic clarity. Most might lean on the ELSE part, but defining every outcome explicitly is the wiser path:

SELECT 
  StatusCode,
  CASE StatusCode
    WHEN 'A' THEN 'Approved' /*Greenlit✅*/
    WHEN 'R' THEN 'Rejected' /*🛑Stop right there*/
    WHEN 'P' THEN 'Pending' /*🐌Getting there...slowly*/
    ELSE 'Unknown Status' /*Let's call it Schroedinger's Status 😸*/
  END AS StatusDescription
FROM 
  ApplicationStatus;

Use CASE in GROUP BY and HAVING clauses

Augment your aggregation data's filtering by mixin' a pinch of CASE in your GROUP BY and HAVING clauses.

SELECT 
  CASE 
    WHEN Age < 30 THEN 'Under 30' /*Young, wild and free 🕺*/
    ELSE '30 and over' /*Experienced and elegant 💃*/
  END AS AgeGroup,
  COUNT(*) AS UserCount
FROM 
  Users
GROUP BY 
  AgeGroup
HAVING 
  COUNT(*) > 100; /*Attention to the popular kids 🏆*/

Handling NULL pointers

The notorious NULL trifles with the mind during CASE formation. Trusted COALESCE or ISNULL functions help:

SELECT 
  CustomerID,
  CASE 
    WHEN COALESCE(Points, 0) > 1000 THEN 'VIP' /*Hail the VIPs 👑*/
    ELSE 'Regular' /*Creeping in the background 👀*/
  END AS CustomerStatus
FROM 
  CustomerLoyalty;