How do I perform an IF...THEN in an SQL SELECT?

Standard SQL doesn’t have an IF...THEN statement inside a SELECT clause like many procedural languages do. Instead, you typically use the CASE expression (supported by most relational database systems) to achieve the same conditional logic. Some databases (e.g., SQL Server) also provide functions like IIF(), while MySQL offers the IF() function. Below, we’ll show you how to replicate “IF…THEN” logic in SQL using these approaches.

1. Using CASE (ANSI Standard)

The most widely supported method is the CASE expression. It follows this pattern:

SELECT
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE default_result
    END AS new_column
FROM table_name;

Example

Imagine you have an Employees table with a column salary. You want to create a column showing “High” for salaries above 100,000, “Medium” for salaries between 50,000 and 100,000, and “Low” otherwise:

SELECT
    employee_id,
    salary,
    CASE
        WHEN salary > 100000 THEN 'High'
        WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_range
FROM Employees;

Key Points

Portability: The CASE expression is part of the ANSI SQL standard, so it works in most databases (MySQL, PostgreSQL, Oracle, SQL Server, etc.).
Flexibility: You can include multiple WHEN conditions.
Default Handling: The ELSE clause catches any rows that don’t match earlier conditions.

2. Using IIF() in SQL Server

If you’re specifically on Microsoft SQL Server (2012+), you can use IIF() for simple two-branch conditions:

SELECT
    employee_id,
    salary,
    IIF(salary > 100000, 'High', 'Not High') AS salary_label
FROM Employees;

IIF(condition, value_if_true, value_if_false)
This is a shorthand for the most basic “either-or” scenario. For more complex logic, stick with CASE.

3. Using IF() in MySQL

MySQL provides an IF() function that works similarly:

SELECT
    employee_id,
    salary,
    IF(salary > 100000, 'High', 'Not High') AS salary_label
FROM Employees;

IF(condition, value_if_true, value_if_false)
Like IIF() in SQL Server, this is convenient for a single condition check. For multiple conditions, use CASE.

4. Handling Multiple Conditions

When you have more than one condition, CASE remains the most robust and readable option across different SQL dialects. If your database has custom functions for conditional logic, they often still can’t handle multiple branches as gracefully as CASE.

5. Performance Considerations

Indexes and Filtering
- If you’re applying the conditional logic in the SELECT list (rather than the WHERE clause), it typically has minimal impact on how the database uses indexes.
Query Readability
- In complex queries, multiple CASE statements can impact readability. Consider building views or using separate columns if your use case is frequent and complicated.

6. Further Your SQL Mastery

If you want to enhance your SQL skills—covering conditional queries, advanced joins, indexing, and more—check out these courses from DesignGurus.io:

Grokking SQL for Tech Interviews
Master SQL fundamentals and real-world interview questions, including tricky conditional logic scenarios.
Grokking Database Fundamentals for Tech Interviews
Dive into indexing, normalization, transactions, and best practices for large-scale data handling.
Relational Database Design and Modeling for Software Engineers
Deepen your understanding of efficient schema design, data modeling, and high-performance SQL patterns.

7. Ready for Interviews or Real-World Projects?

Mock Interviews: Sharpen your SQL and system design prowess with ex-FAANG engineers.
Interview BootCamp: Need a structured plan? The Interview BootCamp provides a 12-week timeline covering coding, system design, and more.
DesignGurus YouTube Channel: Enjoy free tutorials and in-depth sessions on SQL, system design, and coding challenges on the DesignGurus YouTube channel.

Conclusion

Replicating “IF...THEN” logic in SQL is best handled through CASE (the ANSI standard way) or database-specific functions like IIF() (SQL Server) or IF() (MySQL). For multi-condition scenarios, CASE is more versatile. By mastering these constructs, you’ll be better equipped to write clear, efficient queries that handle complex logic.

Explore more about SQL and database design in the comprehensive courses at DesignGurus.io to excel in both interviews and production environments.

CONTRIBUTOR

TechGrind