Menu
Does one need to include an ELSE clause in a CASE expression?
For example, if I wanted to pull the names of animals that are cats and nothing ELSE, could I use this SELECT statement:
SELECT DISTINCT(CASE WHEN animal_type = 'cat' THEN animal_name END) AS cat_names
I know I could just put animal_type = 'cat' in my WHERE clause and then
SELECT DISTINCT cat_names,
but I'd like to know the answer.
204
The CASE statement always goes in the SELECT clause. CASE must include the following components: WHEN , THEN , and END . ELSE is an optional component.
The SQL CASE Expression The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
Another way to use the Case Statement is within the WHERE clause. There, it may be utilized to alter the data fetched by a query based on a condition. Within that context, the Case Statement is ideally suited to both static queries, as well as dynamic ones, such as those that you would find inside a stored procedure.
The first of which is that in most cases, case statements are slightly more efficient than a whole bunch of if statements. However the only real reason why case statements are better that will ever really affect you is that they are a lot easier to read.
You do not need an else clause. If one isn't specified, case will return null. In other words, it acts as though it has an else null clause.
81
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
