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