9.19.11.CASE, NULLIF, COALESCE, CAST Value Expressions
The CASE Expression
There are many situations where you might find it useful to alter the the data returned by a SQL query based on a few rules. For example, you may want to display Customers gender as 'Male' or 'Female' based on whether their title is 'Mr' or one of 'Miss', 'Mrs' or 'Ms'. The CASE expression can easily accommodate this.
The Syntax of CASE is:
CASE WHEN <search-condition> THEN <output> WHEN <search-condition> THEN <output> ... ELSE <output> END
When a <search-condition> is met the corresponding <output> is returned. If no conditions are met then the <output> after is ELSE is returned as a default value.
Example9.34.Using the CASE expression
SELECT Title, CustomerName, CASE WHEN Title = 'Mr' THEN 'Male' WHEN Title = 'Mrs' THEN 'Female' WHEN Title = 'Miss' THEN Female' WHEN Title = 'Ms' THEN 'Female' ELSE 'Unknown' END as Gender, Company FROM Customers
May return values such as:
Title Gender CustomerName Company VARCHAR VARCHAR VARCHAR VARCHAR ___________________________________________________________ Mr Male Thomas Hardy Around the Horn Miss Female Christina Berglund Berglunds shop Mrs Female Hanna Moos Blauer See Delikatessen Mr Male Laurence Lebihan Bon app
There is also a short hand notation for the CASE expression as follows:
CASE <search-parameter> WHEN <search-value> THEN <output> WHEN <search-value> THEN <output> ... ELSE <output> END
This short hand is best demonstrated by the rewrite of the above example as follows:
Example9.35.Using the CASE short-hand expression
SELECT Title, CustomerName, CASE Title WHEN 'Mr' THEN 'Male' WHEN 'Mrs' THEN 'Female' WHEN 'Miss' THEN Female' WHEN 'Ms' THEN 'Female' ELSE 'Unknown' END as Gender, Company FROM Customers
In both cases the ELSE keyword is optional. If ELSE is unspecified then ELSE NULL is implicit.
The NULLIF Expression
The NULLIF expression is a short hand implementation of a special case of the CASE expression for a popular demand. Consider the following CASE expression:
CASE col1 WHEN 'something' THEN NULL ELSE col1 END
This is replaced by the NULLIF expression which achieves the same result using the following, much shorter expression:
NULLIF (col1, 'something')
This is often useful in situations where you have a code to denote a value as unspecified for whatever reason, but in many applications you would rather this was NULL.
The COALESCE Expression
The COALESCE expression is another application of the CASE expression to suit another frequent requirement. The syntax of COALESCE is as follows:
COALESCE (value-1, value-2, ..., value-n)
COALESCE returns the first non-NULL parameter. This is equivalent to
CASE WHEN value-1 IS NOT NULL THEN value-1 WHEN value-2 IS NOT NULL THEN value-2 ... ELSE value-n END
The CAST Expression
SQL has always been considered a strongly typed language,
meaning that you cannot have expressions that contain arbitrary
data types. Casting is invaluable for comparing values that are
obviously compatible but their data types are not, such as
1 = '1' . This attempts
compares an integer with a char which would not work unless one of
the values was cast as follows:
cast('1' as integer) = 1
The CASTING section for more information.