How to write SQL CASE and IF statements and which Is best
When working with SQL to analyze, manipulate, or transform data, one common requirement is to apply conditional logic. This is where SQL’s built-in conditional expressions come into play—most notably the CASE expression and the IF() function.
These tools allow you to evaluate values and return different outputs based on specific conditions, similar to if-else constructs in general-purpose programming languages.
In this article, we’ll dive deep into:
The syntax and structure of
CASEandIFstatements in SQLPractical use cases and working examples
Differences in behaviour across SQL dialects
Guidelines on when to use one over the other
Learn SQL online
Understanding Conditional Logic in SQL
SQL is declarative, meaning we describe what we want, not how to compute it. However, conditional logic is still essential—for things like categorizing records, creating derived columns, handling nulls, or controlling data flow in procedures.
To achieve this, SQL provides conditional constructs that operate on a row-by-row basis:
CASEexpressions: SQL-standard and versatileIF()function: Concise but limited, specific to some SQL dialects (e.g., MySQL)IF...THEN...ELSE: Procedural flow control (used in stored procedures)
This article focuses on the first two, which are used in queries, SELECT statements, and views.
Understanding Conditional Logic in SQL
SQL is declarative, meaning we describe what we want, not how to compute it. However, conditional logic is still essential—for things like categorizing records, creating derived columns, handling nulls, or controlling data flow in procedures.
To achieve this, SQL provides conditional constructs that operate on a row-by-row basis:
CASEexpressions: SQL-standard and versatileIF()function: Concise but limited, specific to some SQL dialects (e.g., MySQL)IF...THEN...ELSE: Procedural flow control (used in stored procedures)
This article focuses on the first two, which are used in queries, SELECT statements, and views.
SQL CASE Expression
The CASE expression is part of standard SQL and is the most powerful and flexible way to write conditional logic in queries. It works similarly to a switch-case in many programming languages or chained if-else blocks.
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
Let’s say you want to classify employees based on their salary ranges. Here’s how you can do it using a CASE expression:
SELECT
name,
department,
salary,
CASE
WHEN salary >= 80000 THEN 'High'
WHEN salary >= 60000 THEN 'Medium'
ELSE 'Low'
END AS salary_level
FROM employees;
Result
| name | department | salary | salary_level |
|---|---|---|---|
| Alice | Sales | 55000 | Low |
| Bob | Engineering | 72000 | Medium |
| Carol | HR | 48000 | Low |
| Dave | Engineering | 83000 | High |
| Eve | Sales | 51000 | Low |
As you can see, the CASE expression evaluates each row’s salary and returns a classification based on the first condition that evaluates to TRUE.
SQL IF() Function
The IF() function is commonly found in MySQL and MariaDB. Unlike CASE, which is standard SQL, IF() is proprietary—but very handy for binary decisions (i.e., “if this then that, otherwise something else”).
IF(condition, value_if_true, value_if_false)
Example:
SELECT
name,
department,
IF(department = 'Engineering', 'Tech Team', 'Other') AS team_type
FROM employees;
Result
| name | department | team_type |
|---|---|---|
| Alice | Sales | Other |
| Bob | Engineering | Tech Team |
| Carol | HR | Other |
| Dave | Engineering | Tech Team |
| Eve | Sales | Other |
This approach is clean and effective when you have a simple condition. However, IF() doesn’t scale well to more complex logic involving multiple branches.
Comparing CASE vs IF() in SQL
| Feature | CASE Expression | IF() Function (MySQL) |
|---|---|---|
| SQL Standard Compliance | ✅ Yes | ❌ No |
| Number of Conditions | ✅ Supports many | ❌ Only one (binary) |
| Readability | ✅ High for complex logic | ✅ High for simple logic |
| Portability (works in all DBs) | ✅ Yes | ❌ Limited to MySQL/MariaDB |
| Use in Procedures | ✅ With CASE, and IF...THEN | ✅ Also supported |
| Nested Conditions | ✅ Fully supported | ⚠️ Possible but messy |
When to Use CASE
When you’re writing SQL for PostgreSQL, SQL Server, Oracle, or SQLite
When you need to check multiple conditions
When writing portable code for use across multiple platforms
When clarity and maintainability are important
When to Use IF()
When working in MySQL and logic is simple
When you want concise syntax
When checking a single binary condition
Please see the other SQL video learning articles below as part of this learning SQL online course video.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
