Quiz: CASE WHEN, IF, COALESCE, IFNULL and NULLIF in SQL (MySQL)
Study the lesson first: 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?
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' ENDaccepts any condition. Branches go from the narrowest to the widest: iflbs >= 15comes first, 54 pounds never reaches the “bulk” branch. - The simple form
CASE product WHEN 'apples' THEN ... WHEN 'lemons' THEN ... ENDcompares one expression against a list of values with=. That is whyCASE price WHEN NULLmatches no row:price = NULLis never true. ForNULLyou need the searched form withIS 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?
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?
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:
COALESCEis standard SQL (MySQL, PostgreSQL, Oracle, SQL Server, H2);IFNULLexists only in MySQL/MariaDB and SQLite. - Number of arguments:
COALESCEtakes any number and walks through them left to right:COALESCE(mobile_phone, work_phone, email);IFNULLtakes exactly two. - Result type in MySQL is the common type of all arguments for both:
IFNULL(price, 'no price')andCOALESCE(price, 'no price')both turn1.50into a string as well. The type difference that comes up in interviews belongs toISNULLin 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?
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 returnsNULLwith warning 1365Division by 0even withoutNULLIF. - In
INSERTandUPDATE, theSTRICT_TRANS_TABLESandERROR_FOR_DIVISION_BY_ZEROmodes are on by default in MySQL 8, and the same division fails withERROR 1365 (22012): Division by 0. - In PostgreSQL even a
SELECTraises 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?
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) skipsNULLarguments 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.COALESCEinsideCONCATkeeps 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?
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) returnnull, and the Java code makes the decision, for example withOptional.ofNullable(rs.getBigDecimal("price")). - Primitive getters (
getInt,getDouble) return zero forNULL. Only awasNull()call right after the getter, before the nextgetXxx, tells it apart from a real zero.