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.

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

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:

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 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 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

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

	See Also:
	The CASTING section for more information.