Control flow in SQL.
By Leonid Toshchev, Nov. 24, 2014, 10:09 p.m.

We can't go past CASE WHEN clause especially when we are speaking of the data aggregation. Despite the fact that the SQL itself is not a programming language, it gives us a great opportunity to use control flow in queries without recourse to the stored procedure. Disadvantages of stored procedure are quite high threshold of entry and inability of writing the cross-platform code.

The principle of clause is a very simple design:

CASE 
WHEN condition1 THEN value1 WHEN condition2 THEN value2 … WHEN conditionN THEN valueN ELSE valueElse END

If the condition in WHEN is true, then in place of the whole clause substitutes the corresponding value. If all conditions are false, then the value from the ELSE part will be taken.

Think about where it might be useful. For example we have a table of products with a price, discount and field that determines whether or not to apply the discount.

SELECT 
  CASE 
    WHEN use_discount THEN discount * price 
    ELSE price 
  END as price 
FROM products

A real case can differ by the presence of a separate table with discounts (then we add JOIN to the query) or a discount on the order date (then we complicate the condition with 'BETWEEN since AND till' statement).

One more example from real life. In order not to clog the head of the reader dozen JOINs we take from example only part associated with CASE WHEN.

Orders and their positions are stored in the database. Positions have statuses: open, edited by the client, closed. status of the order changes depending on the status and position number. It can be: empty, open, edited by the client, closed, transferring. The order is empty if it hasn't any positions. "Transferring" if the status of the positions doesn't match. If all positions have equal status, the same status is assigned to the order. Our task is to display a list of orders with stamped statuses for them. This list will be very convenient for sales managers working with CRM.

Let's try to collect our request from pieces. First, we combine two tables and group them by order's id:

SELECT o.id 
FROM orders o 
  JOIN positions p 
    ON o.id = p.order_id 
GROUP BY o.id 

Now try to pull for each order his state:

SELECT 
  o.id, 
  CASE 
    WHEN count(DISTINCT p.status_id) = 1 THEN max(p.status_id) 
    WHEN count(p.status_id) = 0 THEN 'Empty' 
    ELSE 'Transferring' 
  END 
FROM orders o 
  JOIN positions p 
    ON o.id = p.order_id 
GROUP BY o.id

In the first condition we are using a little hack: if all positions in the order have the same status, then we can perform on it max, min or avg functions. Get exactly the value that we need as the result. Unfortunately, we can not use the status_id field itself, since it is not specified in the GROUP BY.

CASE WHEN clause awesome, but more awesome is the ability to put inside it other CASE WHEN clauses.

Probably, sales managers would not be very happy to look at status_id field, so we replace it with the text, using nested CASE clauses:

SELECT 
  o.id, 
  CASE 
    WHEN count(DISTINCT p.status_id) = 1 THEN 
      CASE max(p.status_id) 
        WHEN 0 THEN 'Opened' 
        WHEN 1 THEN 'Edited by the client' 
        WHEN 2 THEN 'Closed' 
      END 
    WHEN count(p.status_id) = 0 THEN 'Empty' 
    ELSE 'Transferring' 
  END AS status 
FROM orders o 
  LEFT JOIN positions p 
    ON p.order_id = o.id 
GROUP BY o.id

The attentive reader will notice that the outer and inner CASEs are slightly different. In the outer clause conditions are within the WHEN, in the inner, we compare the max(p.status_id) with the values listed in the WHEN. Both alternatives are acceptable and are used depending on the situation. Is there any field that you want to compare with multiple values? Put it to the CASE. Do you have many different conditions? Leave blank CASE and describe conditions in the WHEN part.

SQL is still not a programming language, but its ability to simply and clearly manipulate data amaze and greatly simplifies the life and work of a programmer, same as DBA and analysts.

If you have any comments or suggestions - feel free to email me: mail@datamonkey.pro

Achievement unlocked
A lot of words about why we are so awesome. Or something else. Dont know. Really.