Even though the CASE expression seems to remind us of imperative structures such as IF, SWITCH, and the like, it still does not allow for program flow control like those imperative structures, but rather allows for declarative evaluation of values based on certain conditions. Let's have a look at the following example in order to better understand this feature.

Please enter the following query in the SQL tab of the test database via the phpMyAdmin interface:

SELECT id, COUNT(*) as Total, COUNT(CASE WHEN superior IS NOT NULL THEN id END) as 'Number of superiors'
FROM employees
WHERE id = 2;

This query should yield the following result set:

As the result shows, the row with an id value of 2 was filtered out from the input of the second COUNT function as the CASE expression applied the condition which states that the superior column must not have a NULL value in order to count the id column. Using this feature of Modern SQL is not, for the most part, a question of added performance, but rather a question of avoiding stored procedures and controlling execution flows as much as possible, while keeping the code clean, easily readable and maintainable.