Conditional functions

Conditional functions

Description

Example

CASE WHEN

Simple CASE format

Compares the value of input_exp to the values of value_exp arguments to determine the result.

CASE input_exp
{WHEN value_exp THEN result...} [ELSE result]
END
Copy 
SELECT 
   Invoice_ID, 
   CASE Company_Name
      WHEN 'Exports UK' THEN 'Exports UK Found'
      WHEN 'Home Furniture Suppliers' THEN 'Home Furniture Suppliers Found'
      ELSE 'Neither Exports UK nor Home Furniture Suppliers' 
      END,
   Salesperson_ID
FROM 
   Sales_Data

Searched CASE format

Returns a result based on whether the condition specified by a WHEN expression is true.

CASE 
{WHEN >boolean_exp THEN result...} [ELSE result]
END
Copy 
SELECT 
   Invoice_ID, 
   Amount,
   CASE 
      WHEN Amount > 3000 THEN 'Above 3000'
      WHEN Amount < 1000 THEN 'Below 3000'
   ELSE 'Between 1000 and 3000' 
   END,
   Salesperson_ID
FROM 
   Sales_Data

COALESCE

Returns the first value that is not NULL
Copy 
SELECT
   Salesperson_ID,
   COALESCE(Sales_Manager, Salesperson)
FROM
   Salespeople

NULLIF

Compares two values and returns NULL if the two values are equal; otherwise, returns the first value.
Copy 
SELECT
   Invoice_ID,
   NULLIF(Amount,  -1),
   Salesperson_ID
FROM
   Sales_Data