IF in SQL: IIF, IF() and CASE
Standard SQL has no IF function inside a query. It has CASE WHEN, which does the same job and works in every database. Several databases add a shortcut that reads exactly like Excel's IF:
IIF(test, yes, no)in SQL Server and SQLiteIF(test, yes, no)in MySQL and BigQuery- Postgres and most others: use CASE WHEN
Syntax
IIF(condition, value_if_true, value_if_false)
-- works everywhere:
CASE WHEN condition THEN value_if_true ELSE value_if_false ENDTry itEdit the query, then Run
| name | country | market |
|---|---|---|
| Kavya Haddad | United States | domestic |
| Ben Kim | India | international |
| Kavya Kim | India | international |
| Nikhil Tanaka | Nigeria | international |
| Elena Nguyen | United Kingdom | international |
| Leo Garcia | Nigeria | international |
| Tara Ali | Germany | international |
| Quinn Lopez | United States | domestic |
| Sam Okafor | Brazil | international |
| Ben Smith | United States | domestic |
10 rows
The same thing with CASE, which you can paste into any database:
CASE WHEN country = 'United States' THEN 'domestic' ELSE 'international' ENDYou'll also see IF ... ELSE blocks in SQL Server scripts and stored procedures. Those control which statements run; they don't build a column, so for reports you want IIF or CASE.
Your turn
For each subscription show id and a column status that says 'cancelled' when cancelled_at has a value, otherwise 'active'.
IIF(cancelled_at IS NOT NULL, 'cancelled', 'active').
This page on one card
