CASE… WHEN… ELSE… END

Case… When.. Else… End maybe one of the most used and universal SQL statements, we will dive into it…

Did you know you could use CASE with aggregate functions? Today you will know more about it!

Case When Else End post image

Contents

How should I use CASE… WHEN… ELSE… END?

The statement as described should be used for labeling purposes.

SELECT CASE WHEN user='test user' THEN [DECLARE VARIABLE OR STATEMENT]
WHEN user='test user 2' THEN [DECLARE ANOTHER VARIABLE OR STATEMENT]
ELSE [DECLARE ANOTHER VARIABLE OR STATEMENT]
END AS CASE_TEST FROM TABLE [WHERE ...]

Can I use CASE… WHEN… ELSE… END in the WHERE clause?

Yes, you might use CASE… WHEN… ELSE… END in a WHERE clause.

SELECT * FROM TABLE_USERS WHERE POINTS = CASE WHEN user = 'test user' THEN '100' ELSE '200' END

This will make a difference of equivalence in your SQL statement between the user ‘test user’ and the rest of the users.

How useful is it to use CASE… WHEN… ELSE… END in a subquery?

Actually, it’s very useful, check this query:

SELECT A.* FROM (SELECT user, CASE WHEN user='test user' THEN '100' ELSE '200' END AS needed_points, SUM(POINTS) as total_points FROM TABLE_USER_POINTS GROUP BY 1) A
WHERE A.needed_points < A.total_points

As you can see, here we made a CASE WHEN to differentiate the users as in the previous statement, but then we used that case to set a threshold and get only the users that have a sum of more points than that threshold.

CASE… WHEN… ELSE… END with Aggregate Functions

Let’s start from the basics here, what if you need a message if the COUNT(*) of your table gives you more than 10.000 rows?

SELECT CASE WHEN COUNT(*) > 10000 THEN 'MORE THAN 10 THOUSAND ROWS' ELSE 'LESS OR EQUAL THAN 10 THOUSAND ROWS' END AS ROW_MESSAGE FROM TABLE [WHERE ...]

Counting when one or more conditions are met

This one might be a little tricky at first to understand… But using CASE… WHEN… ELSE… END we could count values when one or more conditions are met. For instance:

SELECT CASE WHEN SUBSTRING(user,1,2)='A' THEN SUM(1) ELSE SUM(0) END AS users_starting_with_A,
CASE WHEN SUBSTRING(user,1,2)='B' THEN SUM(1) ELSE SUM(0) END AS users_starting_with_B,
CASE WHEN (SUBSTRING(user,1,2)<>'A' AND SUBSTRING(user,1,2)<>'B') THEN SUM(1) ELSE SUM(0) END AS users_not_starting_with_A_or_B 
FROM TABLE WHERE [...]

CASE… WHEN… ELSE… END with JOIN statements

Case… WHEN… ELSE… END can also be used in join statements to define a condition to do that join. For example:

SELECT * FROM table_users A
LEFT JOIN table_user_points B
ON A.user=B.user
AND B.points> CASE WHEN A.user='test user' THEN 100 ELSE 200 END 

Here we told SQL to match each user with a left join between tables, but also we are telling SQL that we only want the users with more points than 200 UNLESS it is ‘test user’ which needs more than 100 points.

Conclusion

There are still many more ways of using CASE… WHEN… ELSE… END. We have done this article for an introductory level. As we saw, we could use CASE… WHEN… ELSE… END for a series of different purposes.

To learn more about it I encourage you to read Teradata’s docs files on the matter as is a very complete and universal knowledge base on it.

Leave a Reply