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

To perform an IF-THEN logic in an SQL SELECT query, you can use the CASE expression (standard SQL) or vendor-specific functions like IF() (MySQL) or IIF() (SQL Server). Here’s how to do it:

1. Using CASE (Standard SQL)

The CASE statement allows you to implement IF-THEN-ELSE logic directly in your query. It works in all major databases (PostgreSQL, MySQL, SQL Server, Oracle, etc.).

Syntax:

SELECT
  column1,
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
  END AS new_column_name
FROM table_name;

Example:

SELECT
  name,
  age,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group
FROM users;

Output:

name	age	age_group
Alice	25	Adult
Bob	16	Minor
Charlie	70	Senior

2. Using IF() (MySQL)

MySQL provides a shorthand IF() function for simple IF-THEN-ELSE logic.

Syntax:

SELECT
  IF(condition, result_if_true, result_if_false) AS new_column
FROM table_name;

Example:

SELECT
  name,
  IF(score >= 60, 'Pass', 'Fail') AS result
FROM students;

Output:

name	result
Alice	Pass
Bob	Fail

3. Using IIF() (SQL Server)

SQL Server uses IIF() for concise conditional logic.

Syntax:

SELECT
  IIF(condition, result_if_true, result_if_false) AS new_column
FROM table_name;

Example:

SELECT
  product_name,
  IIF(quantity > 0, 'In Stock', 'Out of Stock') AS status
FROM products;

Output:

product_name	status
Laptop	In Stock
Mouse	Out of Stock

4. Handling NULL Values

Use COALESCE with CASE to handle NULL values:

SELECT
  name,
  CASE
    WHEN address IS NULL THEN 'Unknown'
    ELSE address
  END AS address_info
FROM customers;

Comparison of Methods

Method	Database Support	Use Case
CASE	All databases	Complex logic with multiple conditions
IF()	MySQL	Simple true/false conditions
IIF()	SQL Server	Simple true/false conditions

Advanced Example

SELECT
  order_id,
  CASE
    WHEN total_amount > 1000 THEN 'Premium'
    WHEN total_amount BETWEEN 500 AND 1000 THEN 'Standard'
    ELSE 'Basic'
  END AS order_tier,
  CASE
    WHEN payment_method = 'Credit Card' THEN 'CC'
    WHEN payment_method = 'PayPal' THEN 'PP'
    ELSE 'Other'
  END AS payment_code
FROM orders;

Output:

order_id	order_tier	payment_code
101	Premium	CC
102	Standard	PP
103	Basic	Other

Key Notes

• Use CASE for portable code that works across databases.
• For simple checks, vendor-specific functions like IF() or IIF() can simplify your query.
• Always include an ELSE clause to handle unexpected values (unless you want NULL as the default).