laika222
10/27/2016 - 6:07 PM

CASE Expressions, COALESCE, IFNULL

CASE statement (similar to IF THEN statement). Line 1 tells it to update Orders4 table. Line 2 tells it to update the Product value to the CASE = IF OrderID = 1, THEN update PRODUCT to 'Not Known'. Line 4 says if Order does not = 1, then keep the value the same as was already in the Product field (ELSE can also change the value to something else). Line 6 ends the CASE statement.

/**********************
CASE EXPRESSIONS
**********************/

/*
CASE expressions have two forms - the simple form and the searched form.

The simple form compares an input expression to multiple possible scalar when expressions,
and returns the result expression associated wit hteh first match. If there's no ELSE clause 
specified, the default ELSE is NULL.
*/

UPDATE Orders4
  SET Product = CASE
    WHEN OrderID = 0 THEN 'No'
    WHEN OrderID = 1 THEN 'Yes'
    ELSE Product
  END ;

/*
The searched expression uses predicates in the WHEN clauses, and the first predicate that 
evaluates to true determines which expression is returned.
*/

UPDATE Orders4
  SET Category = CASE
    WHEN unitePrice < 20 THEN 'Low'
    WHEN unitePrice < 40 THEN 'Medium'
    WHEN unitePrice < 60 THEN 'High'
    ELSE 'Super High'
END ;

/**********************
COALESCE
**********************/

/*
COALESCE and ISNULL are seen as abbreviates of the CASE expression.

COALESCE accepts a list of expressions as input and returns the first that is not NULL, or NULL
if all of them are NULLs. 

COALESCE is determined 
*/
SELECT COALESCE(NULL, NULL, 'x', 'y', 'z')

/*
Results:

(No column name)
x

/**********************
ISNULL
**********************/

/*
COALESCE and ISNULL are seen as abbreviates of the CASE expression.

ISNULL is like COALESCE but it only supports two inputs. It returns the first input that is not NULL.
This is proprietary to SQL Server, so if there's a requirement to use standard code, use COALESCE.

*/

/**********************
DIFFERENCES BETWEEN COALESCE AND ISNULL
**********************/

/*
The data type of a COALESCE statement is determined by the returned element, whereas the data type
of ISNULL is determined by the first input. In the example below, for COALESCE @y is the first 
not NULL and therefore the data returned is VARCHAR(10). For ISNULL, @x is the first input, and
therefore the the data type returned is the VARCHAR(3) from @x.
*/
declare @x VARCHAR(3) = NULL, @y VARCHAR(10) = '1234567890'

SELECT COALESCE(@x, @y) AS 'COALESCE Result',
	ISNULL(@x, @y) AS 'ISNULL Result'
	
/* Results:

COALESCE Result   ISNULL Result
1234567890        123

*/

/**********************
NULLIF
**********************/

/*
Standard function. Function accepts two input expressions, returns NULL if they're equal, and
returns the first input if they are not equal.
*/

SELECT cont_dt, cont_amt, recd_amt, NULLIF(cont_amt, recd_amt) AS 'NULLIF' from T_CONTRIBUTION

/* Results:

cont_dt                 cont_amt              recd_amt              NULLIF
----------------------- --------------------- --------------------- ---------------------
2015-04-28 00:00:00.000 0.00                  0.00                  NULL
2013-06-28 00:00:00.000 400000.00             400000.00             NULL
2016-12-09 00:00:00.000 0.00                  0.00                  NULL
2016-12-14 00:00:00.000 105429.00             105429.00             NULL
2017-08-10 00:00:00.000 0.00                  0.00                  NULL
2018-04-27 00:00:00.000 70000.00              0.00                  70000.00
2018-04-27 00:00:00.000 100000.00             0.00                  100000.00
2018-04-27 00:00:00.000 100000.00             0.00                  100000.00
2018-04-27 00:00:00.000 300000.00             0.00                  300000.00

*/