How do I Create an IF-THEN-ELSE Statement in SQL?

In SQL, you can use CASE expressions to implement IF-THEN-ELSE logic. A CASE expression can be used within a SELECT, WHERE, ORDER BY, or other clauses to evaluate conditions and return specific results.

General Syntax

Simple CASE Expression:

This compares one expression to multiple possible values:

Searched CASE Expression:

This allows more complex, condition-based logic:

Examples

1. Using in a SELECT Clause

To categorize values based on conditions:

• Explanation: Each row is evaluated, and the salary_category is assigned based on the employee’s salary.

2. Using in a WHERE Clause

To filter rows dynamically:

• Explanation: The WHERE clause dynamically applies different conditions based on the order_status.

3. Using in an UPDATE Statement

To update values conditionally:

• Explanation: Updates the bonus column based on the performance_rating.

Key Notes

1. Order of Conditions:
   • Conditions are evaluated in the order they appear.
   • The first condition that evaluates to TRUE determines the result.
2. Default with ELSE:
   • Always include an ELSE to handle cases where none of the conditions match.
   • If ELSE is omitted and no conditions are met, the result will be NULL.
3. Portability:
   • The CASE expression is standard SQL and works across most databases (e.g., MySQL, PostgreSQL, SQL Server, Oracle).

Using CASE, you can replicate IF-THEN-ELSE logic flexibly in SQL queries.