Increasing the flow. More control.
By Leonid Toshchev, Dec. 11, 2014, 10:13 p.m.

0. Introduction

It would be wrong to say that all flow control in SQL ends at CASE WHEN in SELECT clause (Control flow in SQL). This method of using has a greater effect on the "visual" component of the result, leaving aside the business logic and optimisation mechanisms.

I can describe at least two more ways of using CASE WHEN clause: within the HAVING/WHERE clause and within the JOIN clause.

The first option I periodically see in the code of my colleagues. Also I used it a few times by myself. Dynamically changing conditions in the HAVING clause is not so popular as the use of CASE in the SELECT block and has its own set of drawbacks, which will be discussed in a separate article. Dynamic binding table even less popular. On the Internet you can find a few examples, but they are too complicated to understand. I never use such practice, but to cover the topic of the CASE WHEN clause completely I invented surrogate example.

But enough words. Begin to practice.

1. CASE in HAVING clause

Today we once again shall feel ourselves in the skin of an analyst of the small online store.

Advertising is the engine of trade. And e-mail newsletter is one of the most effective ways to advertise. Our task is to make a list of people whose payment for November is lower than the average check in their state and push them to buy.

As I already mentioned (Median in SQL) average is not the best way to calculate the mean. Most of the time we are using median. One of our colleagues was so kind that has already calculated it for each USA state where our store has sales. Our task is to make a list of e-mails.

SELECT c.email 
FROM clients c 
JOIN orders o 
  ON o.cid = c.client_id 
JOIN positions p 
  ON o.id = p.order_id 
JOIN products pr 
  ON p.product_id = pr.product_id 
WHERE o.order_date BETWEEN '2014-11-01' AND '2014-12-01' 
GROUP BY c.email, c.state 
HAVING 
  SUM(p.price) < 
  CASE c.state 
    WHEN 'NY' THEN 750 
    WHEN 'VA' THEN 2000 
    WHEN 'PA' THEN 1000 
  END 

So, you can replace argument of the function or operand of the expression by the CASE WHEN clause. The appropriate value will be placed depending on the conditions. The same approach can be used in WHERE clause.

2. CASE in JOIN clause

I wish you would never use a similar approach in a real-world projects. This whole story tale was invented only in order to show you the basics of working with CASE WHEN inside the JOIN clause. You can experiment with examples in our interactive console.

Our shop offers the possibility of several ways for registrations: by entering your email, through the Facebook API and via Twitter API. In the first case we using email as the client ID, in the second client ID is the Facebook ID (fid), in the third it is the Twitter ID (tid). For each of these IDs in the table “clients” was created a field. We find out the type of the registration by reg_type field. Join between the orders and the clients always based on a certain ID defined by the reg_type field.

Our aim is to substitute right ID in the JOIN condition.

SELECT c.name, o.* 
FROM clients c 
JOIN orders o 
  ON CASE 
    WHEN c.reg_type = 1 
      THEN o.client_id = c.email 
    WHEN c.reg_type = 2 
      THEN o.client_id = c.fid 
    WHEN c.reg_type = 3 
      THEN o.client_id = c.tid 
  END

Now we substitute not only one operand or argument, but the whole condition itself.

You can find one of the real examples of using CASE WHEN in the JOIN clause here: Can I use CASE statement in a JOIN condition?

3. Conclusion

CASE WHEN clause provides a simple and intuitive way of the control flow even in the JOIN clause. Furthermore, as part of SQL Standard CASE WHEN clause leaves query a cross-platform. Of course there are some cons. Most of the time CASE clause gives a penalty to the performance. In what cases and how much we consider in the next article.

In the end I want to mention another example of use CASE WHEN in SELECT clause, which was kindly granted by Yawar Amin.

The idea is that if more than one column depends on the result of a complex calculation, we do the calculation in a CTE and express it in the form of a 'result code' like 1, 2, 3, etc., then we use the result code in the next expression to output the final result.

WITH calc_cte as ( 
  SELECT a, b, 
    CASE 
      WHEN complex_condition_1 THEN 1 
      WHEN complex_condition_2 THEN 2 
      ELSE 3 
    END as complex_calculation_result_code 
  FROM table_1 
) 
SELECT a, b, 
  CASE complex_calculation_result_code 
    WHEN 1 THEN result_1 
    WHEN 2 THEN result_2 
    WHEN 3 THEN result_3 
    ELSE result_e 
  END as c 
FROM calc_cte 

Using WITH saves us from using nested CASE WHEN. Though it is not completely cross-platform way.

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.