Conditional Logic (CASE, COALESCE, NULLIF)

Difficulty: Intermediate
Generated on: 2025-07-10 02:25:18
Category: SQL Cheatsheet for Database Development

---

SQL Conditional Logic Cheatsheet (Intermediate)

This cheatsheet covers conditional logic in SQL using CASE, COALESCE, and NULLIF. It’s designed to be a practical, developer-friendly reference for real-world scenarios.

1. Quick Overview

Feature	Description	When to Use
CASE	Allows you to define conditional expressions within a SQL statement. It’s similar to if-then-else logic in programming languages. It evaluates conditions and returns a corresponding value based on which condition is met first.	When you need to return different values based on specific conditions, categorize data, or perform conditional calculations within a query.
COALESCE	Returns the first non-NULL expression in a list of expressions. It’s useful for providing default values when a column might contain NULLs.	When you need to handle NULL values by substituting them with a default value. For example, displaying “N/A” instead of NULL in a report or using a default value in a calculation.
NULLIF	Returns NULL if two expressions are equal; otherwise, it returns the first expression. It’s helpful for preventing division by zero or other errors that can occur when comparing potentially equal values.	When you need to prevent errors caused by comparing or dividing by values that might be equal, especially when one of the values could be zero. It’s often used to handle edge cases in calculations.

2. Syntax

CASE Statement

-- Simple CASE
CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE resultN
END

-- Searched CASE
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE resultN
END

COALESCE Function

COALESCE(expression1, expression2, ..., expressionN)

NULLIF Function

NULLIF(expression1, expression2)

3. Common Use Cases

• CASE:
  • Categorizing data based on ranges (e.g., age groups, sales tiers).
  • Converting numeric or string values to different representations.
  • Calculating aggregate values conditionally.
  • Creating custom flags or indicators based on complex criteria.
• COALESCE:
  • Providing default values for missing data.
  • Concatenating strings where some strings might be NULL.
  • Selecting the first available value from multiple columns.
• NULLIF:
  • Preventing division by zero errors.
  • Handling edge cases in calculations where equal values would cause issues.
  • Simplifying conditional logic in some scenarios.

4. Best Practices

• CASE:
  • Use ELSE to handle unexpected conditions and avoid NULL results.
  • Order WHEN clauses logically for efficiency (most common conditions first).
  • Keep CASE statements concise and readable. Consider breaking down complex logic into smaller, more manageable parts.
  • When possible, avoid nested CASE statements as they can become difficult to read and maintain.
• COALESCE:
  • Ensure the data types of all expressions in COALESCE are compatible to avoid implicit conversions and potential errors.
  • Use COALESCE strategically to improve query readability by handling NULLs directly within the query rather than in application code.
• NULLIF:
  • Use NULLIF judiciously, as it can sometimes obscure the logic of your query.
  • Consider the potential performance impact of NULLIF, especially in large datasets.

5. Examples

Sample Data

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary DECIMAL(10, 2),
    DepartmentID INT,
    Bonus DECIMAL(10,2) NULL
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Engineering');

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary, DepartmentID, Bonus) VALUES
(1, 'John', 'Doe', 60000.00, 1, 1000.00),
(2, 'Jane', 'Smith', 75000.00, 2, NULL),
(3, 'Peter', 'Jones', 90000.00, 3, 2000.00),
(4, 'Alice', 'Brown', 55000.00, 1, NULL),
(5, 'Bob', 'Miller', 80000.00, 3, 1500.00);

CASE Examples

-- Categorize employees based on salary
SELECT
    EmployeeID,
    FirstName,
    LastName,
    Salary,
    CASE
        WHEN Salary < 60000 THEN 'Entry-Level'
        WHEN Salary >= 60000 AND Salary < 80000 THEN 'Mid-Level'
        ELSE 'Senior'
    END AS SalaryLevel
FROM Employees;

-- Output:
-- EmployeeID | FirstName | LastName | Salary  | SalaryLevel
-- -----------|-----------|----------|---------|-------------
-- 1          | John      | Doe      | 60000.00| Mid-Level
-- 2          | Jane      | Smith    | 75000.00| Mid-Level
-- 3          | Peter     | Jones    | 90000.00| Senior
-- 4          | Alice     | Brown    | 55000.00| Entry-Level
-- 5          | Bob       | Miller   | 80000.00| Senior

-- Calculate the total compensation including bonus, handling NULL bonuses
SELECT
    EmployeeID,
    FirstName,
    LastName,
    Salary,
    Bonus,
    CASE
        WHEN Bonus IS NULL THEN Salary  -- If bonus is null, total compensation equals salary
        ELSE Salary + Bonus
    END AS TotalCompensation
FROM Employees;

-- Output:
-- EmployeeID | FirstName | LastName | Salary  | Bonus   | TotalCompensation
-- -----------|-----------|----------|---------|---------|-----------------
-- 1          | John      | Doe      | 60000.00| 1000.00 | 61000.00
-- 2          | Jane      | Smith    | 75000.00| NULL    | 75000.00
-- 3          | Peter     | Jones    | 90000.00| 2000.00 | 92000.00
-- 4          | Alice     | Brown    | 55000.00| NULL    | 55000.00
-- 5          | Bob       | Miller   | 80000.00| 1500.00 | 81500.00

-- Conditional aggregation
SELECT
    DepartmentID,
    SUM(CASE WHEN Salary > 70000 THEN 1 ELSE 0 END) AS HighSalaryCount,
    SUM(CASE WHEN Salary <= 70000 THEN 1 ELSE 0 END) AS LowSalaryCount
FROM Employees
GROUP BY DepartmentID;

-- Output:
-- DepartmentID | HighSalaryCount | LowSalaryCount
-- -----------|-----------------|----------------
-- 1          | 0               | 2
-- 2          | 1               | 0
-- 3          | 2               | 0

COALESCE Examples

-- Display bonus, or "N/A" if bonus is NULL
SELECT
    EmployeeID,
    FirstName,
    LastName,
    COALESCE(CAST(Bonus AS VARCHAR), 'N/A') AS Bonus
FROM Employees;

-- Output:
-- EmployeeID | FirstName | LastName | Bonus
-- -----------|-----------|----------|-------
-- 1          | John      | Doe      | 1000.00
-- 2          | Jane      | Smith    | N/A
-- 3          | Peter     | Jones    | 2000.00
-- 4          | Alice     | Brown    | N/A
-- 5          | Bob       | Miller   | 1500.00

-- Calculate total compensation using COALESCE to handle NULL bonuses (default to 0)
SELECT
    EmployeeID,
    FirstName,
    LastName,
    Salary + COALESCE(Bonus, 0) AS TotalCompensation
FROM Employees;

-- Output:
-- EmployeeID | FirstName | LastName | TotalCompensation
-- -----------|-----------|----------|-----------------
-- 1          | John      | Doe      | 61000.00
-- 2          | Jane      | Smith    | 75000.00
-- 3          | Peter     | Jones    | 92000.00
-- 4          | Alice     | Brown    | 55000.00
-- 5          | Bob       | Miller   | 81500.00

NULLIF Examples

-- Prevent division by zero (hypothetical example, Employees table doesn't have a 'sales' column)
-- Assuming we have a column named 'Sales' and 'TargetSales'
-- SELECT
--     EmployeeID,
--     Sales / NULLIF(TargetSales, 0) AS SalesPerformance
-- FROM Employees;

-- This would return NULL if TargetSales is 0, preventing a division by zero error.
-- Calculate the performance ratio, returning NULL if Sales and TargetSales are equal
-- Assuming we have a column named 'Sales' and 'TargetSales'
-- SELECT
--     EmployeeID,
--     CASE
--         WHEN NULLIF(Sales, TargetSales) IS NULL THEN NULL
--         ELSE Sales / TargetSales
--     END AS PerformanceRatio
-- FROM Employees;

6. Common Pitfalls

• CASE:
  • Forgetting the ELSE clause, which can lead to unexpected NULL values.
  • Incorrectly ordering WHEN clauses, causing unintended results.
  • Nested CASE statements becoming too complex and difficult to understand.
  • Using CASE for simple boolean logic that could be handled with WHERE clauses.
• COALESCE:
  • Incompatible data types among the expressions.
  • Assuming COALESCE handles all possible NULL scenarios without considering other potential issues.
  • Using COALESCE unnecessarily when a default value is already enforced at the database level (e.g., using a DEFAULT constraint).
• NULLIF:
  • Misunderstanding the order of expressions, leading to incorrect NULL results.
  • Using NULLIF when a more explicit CASE statement would be clearer.
  • Not considering the potential impact on indexes or query performance.

7. Database Variations

• MySQL: Syntax is generally consistent with ANSI SQL.
• PostgreSQL: Supports standard SQL syntax and has additional extensions for CASE and COALESCE. No known major differences.
• SQL Server: Supports standard SQL syntax. ISNULL() function is similar to COALESCE but only accepts two arguments and is SQL Server specific. COALESCE is generally preferred for ANSI SQL compatibility.
• Oracle: Supports standard SQL syntax. NVL() function is similar to COALESCE but only accepts two arguments and is Oracle specific. COALESCE is generally preferred for ANSI SQL compatibility.

Important Notes:

• Always test your SQL queries thoroughly before deploying them to production.
• Use appropriate data types for your columns and expressions.
• Consider performance implications when using conditional logic in large datasets.
• Follow security best practices to prevent SQL injection vulnerabilities. Parameterize your queries and avoid concatenating user input directly into SQL statements.
• Consider using views or stored procedures to encapsulate complex conditional logic for reusability and maintainability.