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.