IF() / IIF() Functions

-- MySQL: Returns true_value if the condition is true, false_value otherwise.
SELECT IF(condition, true_value, false_value);

-- SQL Server: IIF behaves the same as IF (SQL Server 2012 and later).
SELECT IIF(condition, true_value, false_value);

-- Nested IF (readability decreases; CASE WHEN is recommended instead).
SELECT IF(condition1, value1, IF(condition2, value2, value3));

SQL (MySQL)

-- Sample data: a fighters table with combat power values.
-- CREATE TABLE fighters (
--     id      INT PRIMARY KEY,
--     name    VARCHAR(20) NOT NULL,
--     power   INT,         -- Combat power (NULL means unmeasured).
--     is_saiyan TINYINT(1) -- 1: Saiyan, 0: Earthling or other.
-- );

-- Use IF to check whether combat power is 5000 or above.
SELECT name, power,
       IF(power >= 5000, 'Elite Warrior', 'Common Warrior') AS rank
FROM fighters;

-- IIF (for SQL Server) produces the same result.
-- SELECT name, power,
--        IIF(power >= 5000, 'Elite Warrior', 'Common Warrior') AS rank
-- FROM fighters;

-- Use IF to display a label indicating whether a fighter is a Saiyan.
SELECT name,
       IF(is_saiyan = 1, 'Saiyan', 'Other') AS race_label
FROM fighters;

-- Combine COALESCE for columns that may contain NULL (unmeasured).
-- Writing this with IF alone becomes verbose; COALESCE is recommended.
SELECT name,
       COALESCE(power, 0)                                  AS power_display,
       IF(COALESCE(power, 0) >= 5000, 'Elite', 'Unknown') AS rank
FROM fighters;

-- Combine NULLIF and IF to avoid division by zero.
-- battles: number of matches, wins: number of wins.
SELECT name,
       IF(NULLIF(battles, 0) IS NULL,
          NULL,
          wins / NULLIF(battles, 0)) AS win_rate
FROM fighters;

-- Nested IF (three-level branching) reduces readability.
-- Use CASE WHEN for three or more branches.
SELECT name, power,
       IF(power >= 8000, 'Super Warrior',
          IF(power >= 5000, 'Elite Warrior',
             'Common Warrior')) AS rank_nested  -- Hard to read; CASE WHEN is recommended.
FROM fighters;

-- A more readable version rewritten with CASE WHEN.
SELECT name, power,
       CASE
           WHEN power >= 8000 THEN 'Super Warrior'
           WHEN power >= 5000 THEN 'Elite Warrior'
           ELSE 'Common Warrior'
       END AS rank_case
FROM fighters;

Result (SELECT name, power, IF(...) AS rank FROM fighters;)

-- +--------+-------+----------------+
-- | name   | power | rank           |
-- +--------+-------+----------------+
-- | Goku   |  9000 | Elite Warrior  |
-- | Vegeta |  8500 | Elite Warrior  |
-- | Gohan  |  3000 | Common Warrior |
-- | Piccolo|  3500 | Common Warrior |
-- | Krillin|  1500 | Common Warrior |
-- +--------+-------+----------------+

Result (SELECT name, CASE WHEN ... END AS rank_case FROM fighters;)

-- +--------+-------+---------------+
-- | name   | power | rank_case     |
-- +--------+-------+---------------+
-- | Goku   |  9000 | Super Warrior |
-- | Vegeta |  8500 | Super Warrior |
-- | Gohan  |  3000 | Common Warrior|
-- | Piccolo|  3500 | Common Warrior|
-- | Krillin|  1500 | Common Warrior|
-- +--------+-------+---------------+

IF is a MySQL-specific function and is not part of standard SQL. IIF is a SQL Server-specific function (2012 and later). Use the standard SQL CASE WHEN when portability is required.

-- MySQL: Use the IF function.
SELECT name, IF(power >= 5000, 'Elite Warrior', 'Common Warrior') AS rank FROM fighters;

-- SQL Server: Use the IIF function (SQL Server 2012 and later).
SELECT name, IIF(power >= 5000, 'Elite Warrior', 'Common Warrior') AS rank FROM fighters;

-- Standard SQL (works in MySQL, PostgreSQL, Oracle, SQL Server, and SQLite).
SELECT name,
       CASE WHEN power >= 5000 THEN 'Elite Warrior' ELSE 'Common Warrior' END AS rank
FROM fighters;

PostgreSQL, Oracle, and SQLite do not have an IF function; use CASE WHEN instead. For NULL replacement, the standard SQL COALESCE function is available in all databases.

IF and IIF allow binary branching to be written concisely, but they are vendor-specific functions available only in MySQL and SQL Server. When portability matters, or when three or more branches are needed, use the standard SQL CASE WHEN.

Nested IF (IF(condition1, value1, IF(condition2, value2, value3))) works, but becomes very hard to read as the nesting depth increases. When branching reaches three or more levels, switching to CASE WHEN without hesitation is recommended.

For NULL replacement, writing COALESCE is more concise and portable than IF(column IS NULL, fallback, column). To avoid division by zero, combining with NULLIF is the common approach.