SQL SELECT IF...THEN: Complete Guide

You can implement IF…THEN logic in SQL SELECT statements using the CASE expression, which is the standard approach across all database systems. While some databases offer specific functions like MySQL’s IF() or SQL Server’s IIF(), the CASE expression provides the most portable and flexible solution for conditional logic in queries.

Contents

• Standard CASE WHEN Syntax
• Database-Specific Alternatives
• Practical Examples and Use Cases
• Advanced Conditional Logic
• Best Practices

Standard CASE WHEN Syntax

The CASE expression is the universal way to implement IF…THEN logic in SQL. It comes in two main forms:

Simple CASE Expression

SELECT 
    CASE 
        WHEN Obsolete = 'N' OR InStock = 'Y' THEN 1 
        ELSE 0 
    END AS Saleable,
    * 
FROM Product

Searched CASE Expression

SELECT 
    CASE 
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE default_result
    END AS alias_name
FROM table_name

As the SQL standard specifies, SQL tests WHEN conditions in the order they appear, and if no condition is met, it defaults to NULL unless an ELSE clause is provided.

Database-Specific Alternatives

MySQL

MySQL supports a direct IF() function that works like the ternary operator in your example:

SELECT IF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Saleable, * FROM Product

MySQL also allows COUNT with IF:

SELECT COUNT(IF(status = 'shipped', 1, NULL)) AS shipped_orders FROM orders

SQL Server

SQL Server provides IIF() for simple conditions (available since SQL Server 2012):

SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Saleable, * FROM Product

Oracle

Oracle has DECODE() as a legacy alternative to CASE:

SELECT DECODE(Obsolete, 'N', 1, DECODE(InStock, 'Y', 1, 0)) AS Saleable, * FROM Product

PostgreSQL

PostgreSQL supports standard CASE plus the FILTER clause for aggregates:

SELECT 
    COUNT(*) FILTER (WHERE status = 'shipped') AS shipped_orders 
FROM orders

Practical Examples and Use Cases

Data Categorization

SELECT 
    ID,
    CASE 
        WHEN Salary > 100000 THEN 'High'
        WHEN Salary BETWEEN 50000 AND 100000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryLevel
FROM Employees

Conditional Aggregation

SELECT 
    department,
    SUM(CASE WHEN performance = 'High' THEN salary ELSE 0 END) AS high_performer_salaries,
    COUNT(CASE WHEN performance = 'High' THEN 1 END) AS high_performer_count
FROM employees
GROUP BY department

Complex Business Logic

SELECT 
    name, 
    salary,
    CASE 
        WHEN department = 'Sales' AND salary > 80000 THEN 'High Performer'
        WHEN department = 'Sales' OR salary > 100000 THEN 'Target for Bonus'
        WHEN NOT (department IN ('HR', 'Legal')) THEN 'Eligible for Stock'
        ELSE 'Standard'
    END AS employee_category
FROM employees

Conditional Counting

As InterviewQuery explains, you can use CASE WITH for conditional counting:

-- Standard CASE WHEN approach (portable)
SELECT COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders FROM orders;

-- PostgreSQL-specific FILTER approach
SELECT COUNT(*) FILTER (WHERE status = 'shipped') AS shipped_orders FROM orders;

Advanced Conditional Logic

Nested CASE Expressions

SELECT 
    CASE 
        WHEN status = 'shipped' THEN 
            CASE WHEN shipping_method = 'express' THEN 'Express Shipped' 
                 ELSE 'Standard Shipped' 
            END
        WHEN status = 'pending' THEN 'Pending'
        ELSE 'Unknown'
    END AS delivery_status
FROM orders

Multiple Conditions with AND/OR

SELECT 
    CASE 
        WHEN (department = 'Sales' AND sales > 100000) OR 
             (department = 'Marketing' AND campaigns > 50) THEN 'Top Performer'
        WHEN department IN ('HR', 'Legal') THEN 'Support'
        ELSE 'Regular'
    END AS performance_category
FROM employees

NULL Handling

SELECT 
    CASE 
        WHEN value IS NULL THEN 'No Value'
        WHEN value > 0 THEN 'Positive'
        WHEN value < 0 THEN 'Negative'
        ELSE 'Zero'
    END AS value_category
FROM numbers

Best Practices

1. Use standard CASE for maximum portability - It works across all database systems
2. Order conditions logically - SQL evaluates conditions in order, so place most specific conditions first
3. Always include ELSE clause - Avoids unexpected NULL results
4. Use appropriate data types - Ensure CASE expressions return consistent data types
5. Consider performance - Complex CASE expressions may impact query performance
6. Use database-specific functions when appropriate - For simpler conditions, MySQL’s IF() or SQL Server’s IIF() can improve readability

The Oracle documentation emphasizes that CASE expressions are particularly useful because they allow you to implement conditional logic in SQL statements without needing to invoke stored procedures.

Sources

1. SQL MAX + CASE WHEN Explained (2025 Guide) - InterviewQuery
2. Explore Conditional Expressions in BigQuery: A Complete 2025 Guide - Owox
3. Conditional Selection Statements - Oracle Documentation
4. SQL COUNT with CASE WHEN: Conditional Counting Guide (2025) - InterviewQuery
5. SQL syntax - Wikipedia
6. CASE Expressions - Oracle Documentation
7. Top 100 SQL Interview Questions and Answers (2025) - GeeksforGeeks
8. SQL Guide Engineers Wish They Had - Medium

Conclusion

• Implement IF…THEN logic in SQL SELECT statements primarily using the CASE expression, which is universally supported across all database systems
• MySQL offers the direct IF() function for simpler conditional logic, while SQL Server provides IIF() and Oracle has DECODE() as alternatives
• CASE expressions can handle complex business logic, data categorization, and conditional aggregation
• Always include an ELSE clause to avoid unexpected NULL results and consider performance implications of complex conditional logic
• Choose between standard CASE (for portability) and database-specific functions (for readability) based on your specific requirements and environment