CASЕ
This instruction gets the possibility of fork organizing while value computing. When DBMS meets this instruction it computes first condition and when it is true the first resulting expression is calculated. If the first condition false the second one is checked and if it is true the second resulting expression is executed and so on.
Divide clients on three categories in accordance with the sums of their credit limit.
SELECT company, CASE WHEN credit_limit > 60000 THEN ‘A’ WHEN credit_limit > 30000 THEN ‘B’ ELSE ‘C’ END FROM customers
Any expression could be used as CASE instruction result. It is not necessary all checks placed in WHEN statements were analogical, moreover CASE operation may be present not only in SELECT statement.
If you need to compare the result of some expression with several values ( more often literal values) then instead of repetition of the conditions like checked_expression = value
In every WHEN statement, you can place the checked expression just after CASE keyword in order to calculate it only once for each record and just compare it ‘s value to the WHEN statements check-values.
Show the list of all company offices with their managers’names and names, cities and states they are located in.
SELECT name, city, CASE office WHEN 11 THEN ‘NEW York’ WHEN 12 THEN ‘ Illinois’ WHEN 13 THEN ‘Georgia’ END FROM offices, salesreps WHERE MGR = empl_num
COALESCE
This is special kind of “CASE” operator. The query could be simplified in some cases with using COALESCE
Show the report about employees and their sales plans. If employee has no plan - show his real amount of sales , otherwise show zero.
SELECT name, COALESCE ( quota, sales, 0.00 ) FROM salesreps
Performing this query DBMS calculates the first expression in the COALESCE list. If its value is not equal to NULL, it becomes the result of the whole COALESCE command. In opposite case DBMS switches to the second expression in the list and checks whether it is equal to NULL. If not it returns as a result otherwise DBMS moves to the third expression and so on till not-NULL value will be found.
NULL IF
In some cases absent data are represented not as NULL values but as special codes which can’t be interpreted as data. If you need to reveal such codes and to change them on NULL, you can make this with NULL IF operation. When DBMS meets such instruction, the first expression is analyzing ( usually it is the field name ) and compares its value with the value of the second expression ( usually code one being the absent data ). If they are equal NULL becomes the result of NULL IF function, in another case - the first expression value ( becomes the result ).
In the given query zero means absence of office number:
SELECT city, SUM ( salesreps.sales ) FROM offices, salesreps WHERE office = NULL IF ( rep_office, 0 ) GROUP BY city