Tutorial by Examples

The searched CASE returns results when a boolean expression is TRUE. (This differs from the simple case, which can only check for equivalency with an input.) SELECT Id, ItemId, Price, CASE WHEN Price < 10 THEN 'CHEAP' WHEN Price < 20 THEN 'AFFORDABLE' ELSE 'EXPENSIVE' E...

Use Case CASE can be used in conjunction with SUM to return a count of only those items matching a pre-defined condition. (This is similar to COUNTIF in Excel.) The trick is to return binary results indicating matches, so the "1"s returned for matching entries can be summed for a count o...

CASE's shorthand variant evaluates an expression (usually a column) against a series of values. This variant is a bit shorter, and saves repeating the evaluated expression over and over again. The ELSE clause can still be used, though: SELECT Id, ItemId, Price, CASE Price WHEN 5 THEN 'CHEAP' ...

We can use 1,2,3.. to determine the type of order: SELECT * FROM DEPT ORDER BY CASE DEPARTMENT WHEN 'MARKETING' THEN 1 WHEN 'SALES' THEN 2 WHEN 'RESEARCH' THEN 3 WHEN 'INNOVATION' THEN 4 ELSE 5 END, CITY IDREGIONCITYDEPARTMENTEMPLOYEES_N...

sample on price increases: UPDATE ItemPrice SET Price = Price * CASE ItemId WHEN 1 THEN 1.05 WHEN 2 THEN 1.10 WHEN 3 THEN 1.15 ELSE 1.00 END

in this way '0' representing the known values ​​are ranked first, '1' representing the NULL values ​​are sorted by the last: SELECT ID ,REGION ,CITY ,DEPARTMENT ,EMPLOYEES_NUMBER FROM DEPT ORDER BY CASE WHEN REGION IS NULL THEN 1 ELSE 0 END, REGION ...

Imagine that you need sort records by lowest value of either one of two columns. Some databases could use a non-aggregated MIN() or LEAST() function for this (... ORDER BY MIN(Date1, Date2)), but in standard SQL, you have to use a CASE expression. The CASE expression in the query below looks at th...