Quiz: CASE WHEN, IF, COALESCE, IFNULL and NULLIF in SQL (MySQL)

Total: 6 questions

1. 

How does CASE WHEN work in SQL, and how does the searched form of CASE differ from the simple form?

CASE is the standard SQL conditional expression, the equivalent of if / else if / else. It checks the WHEN conditions in order and returns the THEN value of the first true branch; if none matches, it returns the ELSE value, and without ELSE — NULL.

  • The searched form CASE WHEN lbs >= 30 THEN 'bulk' WHEN lbs >= 15 THEN 'medium' ELSE 'small' END accepts any condition. Branches go from the narrowest to the widest: if lbs >= 15 comes first, 54 pounds never reaches the “bulk” branch.
  • The simple form CASE product WHEN 'apples' THEN ... WHEN 'lemons' THEN ... END compares one expression against a list of values with =. That is why CASE price WHEN NULL matches no row: price = NULL is never true. For NULL you need the searched form with IS NULL.

CASE works anywhere an expression is allowed: in SELECT, WHERE, ORDER BY, UPDATE ... SET. In ORDER BY it defines an order that is not alphabetical: ORDER BY CASE shop WHEN 'Delta' THEN 1 WHEN 'Acme' THEN 2 ELSE 3 END. In WHERE it sets a different threshold per value, but the optimizer usually finds the same condition easier to analyze when written with AND/OR. Counting by a condition, COUNT(CASE WHEN ... THEN 1 END), belongs to aggregate functions.

2. 

How does the IF function work in MySQL, and which should you use: CASE or IF?

IF(condition, value_if_true, value_otherwise) is a shorthand for a one-branch CASE: IF(lbs >= 30, 'wholesale', 'retail'). The function exists only in MySQL and MariaDB; PostgreSQL, Oracle and standard SQL do not have it, so portable code uses CASE WHEN ... THEN ... ELSE ... END.

IF treats a condition that evaluates to NULL as false: IF(price > 3, 'premium', 'regular') returns “regular” for the lemons without a price, although nothing is known about their price. If an unknown value needs its own label, test for it first: CASE WHEN price IS NULL THEN 'no price' WHEN price > 3 THEN 'premium' ELSE 'regular' END.

Which to choose: for one condition with two outcomes IF is shorter. CASE is standard SQL, takes any number of branches and reads better than nested IF(..., ..., IF(...)) calls. There is no noticeable speed difference: both are evaluated for each row.

3. 

What is the difference between COALESCE and IFNULL in MySQL, and does MySQL have NVL and ISNULL?

COALESCE(a, b, ...) returns the first argument that is not NULL; IFNULL(a, b) does the same for two arguments. With two arguments in MySQL they return the same value at the same speed; they differ as follows:

  • Standard: COALESCE is standard SQL (MySQL, PostgreSQL, Oracle, SQL Server, H2); IFNULL exists only in MySQL/MariaDB and SQLite.
  • Number of arguments: COALESCE takes any number and walks through them left to right: COALESCE(mobile_phone, work_phone, email); IFNULL takes exactly two.
  • Result type in MySQL is the common type of all arguments for both: IFNULL(price, 'no price') and COALESCE(price, 'no price') both turn 1.50 into a string as well. The type difference that comes up in interviews belongs to ISNULL in SQL Server.

For code that may move to another database, and for tests on H2, COALESCE is the safer choice. Equivalents in other databases: NVL in Oracle (MySQL does not have it), ISNULL(a, b) in SQL Server; Oracle’s DECODE is replaced by CASE. In MySQL, ISNULL(x) does not substitute a value but tests it: it takes one argument and returns 1 or 0, and ISNULL(price, 0) fails with ERROR 1582: Incorrect parameter count.

4. 

What does the NULLIF function do, and why do you need it if MySQL already returns NULL on division by zero?

NULLIF(a, b) returns NULL if the arguments are equal and a if they are not: NULLIF(0, 0) = NULL, NULLIF('abc', '') = abc. It is the reverse of COALESCE: that one removes NULL, while NULLIF turns a chosen value into NULL.

Its main use is guarding against division by zero: x / NULLIF(y, 0). Dividing by NULL gives NULL, so you get an empty value instead of an error.

  • In a SELECT, MySQL returns NULL with warning 1365 Division by 0 even without NULLIF.
  • In INSERT and UPDATE, the STRICT_TRANS_TABLES and ERROR_FOR_DIVISION_BY_ZERO modes are on by default in MySQL 8, and the same division fails with ERROR 1365 (22012): Division by 0.
  • In PostgreSQL even a SELECT raises an error.

A denominator written as x / NULLIF(y, 0) behaves the same everywhere. Another trick with NULLIF is COALESCE(NULLIF(TRIM(phone), ''), 'not provided'): an empty string and NULL are handled the same way.

5. 

Why does CONCAT return NULL, and how do you join strings in MySQL when one of the values is NULL?

CONCAT follows the general rule of SQL functions: if any argument is NULL, the result is NULL too. The label CONCAT(product, ': ', price) for the grapes without a price returns not “grapes: ” but NULL — the whole string disappears because of one empty column.

There are two ways to fix it:

  • CONCAT_WS(separator, ...) (with separator) skips NULL arguments and puts the separator only between the remaining values: CONCAT_WS(', ', shop, product, price) → Acme, grapes. Use it when a missing value can simply be left out.
  • COALESCE inside CONCAT keeps the shape: CONCAT(product, ': ', COALESCE(price, 'price not set')) → grapes: price not set.

The || operator does not work for joining in MySQL: by default it means logical OR, not concatenation. Oracle treats NULL as an empty string when concatenating with ||, PostgreSQL does not, so portable code that deals with NULL is better written with CONCAT and COALESCE.

6. 

Where should NULL be handled when Java reads from the database: with COALESCE in the SQL query or in the code via JDBC?

A column that may be NULL can be handled in two places: replace the value with COALESCE right in the query, so Java gets a ready value, or read it as is and let the Java code decide.

String sql = "SELECT price, COALESCE(price, 0) AS price_or_zero FROM sales WHERE shop = ?";
BigDecimal price  = rs.getObject("price", BigDecimal.class);  // null for NULL
BigDecimal orZero = rs.getBigDecimal("price_or_zero");        // 0.00 for NULL
double primitive  = rs.getDouble("price");                    // 0.0 for NULL
boolean wasNull   = rs.wasNull();                             // true: refers to the last column read
  • COALESCE in the query suits a report where a missing price counts as zero: the code is shorter and the totals match the SQL. But it erases the difference between zero and “unknown”: the grapes without a price reach Java as 0.00, and the code can no longer tell them from a free item.
  • Reading the column as is is right for a domain object. Object getters (getBigDecimal, getString, getObject) return null, and the Java code makes the decision, for example with Optional.ofNullable(rs.getBigDecimal("price")).
  • Primitive getters (getInt, getDouble) return zero for NULL. Only a wasNull() call right after the getter, before the next getXxx, tells it apart from a real zero.
Page 1 of 1