Database Related

What Is The SQL CASE Statement?

The SQL CASE statement is SQL’s way of handling IF-THEN-ELSE logic statements. What are the IF-THEN-ELSE logic statements? They are conditional statements that behave according to the given condition (i.e. if A = B then do something else do something different). You can use the CASE statement to give your SQL query several alternatives for better functioning to get desired results. These alternatives are given through conditions that are applied within the CASE statement. Here is the simple syntax for the CASE statement in SQL:

CASE Value

WHEN EqaulityCondition1 THEN Do1

WHEN EqualityCondition1 THEN Do2

.

.

.

ELSE EndDo

END

In the above syntax, the following arguments need to be understood:

  • CASE statement marks the start of the logic statements.
  • WHEN statements are used for checking whether Value is equal to the specified Equality Condition.
  • THEN statements are executed if the corresponding WHEN statement condition is true.
  • ELSE statement is only executed when all the WHEN statement conditions are
  • END statement marks the end of the CASE statement in SQL.

The SQL CASE statement runs in the order that we specify i.e. it evaluates the input expression first and then makes the comparison with Condition1 and Condition2 and so on until the ELSE statement is reached. If at any point WHEN Condition statement becomes true, at that time THEN Result statement for that particular condition is executed and SQL breaks out of the CASE statement.

One important thing to note here is that using this simple CASE expression can only make use of equality (=) checks on the given parameter. For allowing Boolean expressions to be used for comparison, the searched CASE syntax needs to be used.

The searched CASE syntax is as follows:

CASE

WHEN BooleanCondition1 THEN Do1

WHEN BooleanCondition2 THEN Do2

.

.

.

ELSE EndDo

END

The execution and arguments for the searched CASE statement are roughly the same with the only difference being in the comparison method. The only difference is that in the searched CASE statement, a Boolean expression (such as age > 18) has to be provided within the WHEN statement.

Here is an example of the use of the CASE statement in SQL:

CASE

WHEN age > 18 THEN ‘Adult’

WHEN age > 12 AND age <= 18 THEN ‘Teenager’

ELSE ‘Child’

END

The above SQL statements produce outputs appropriate for the value of the parameter ‘age’. Note that in the second WHEN statement, we used age > 12 AND age <= 18, which prevents overlapping. Here, we could have used age > 12 and it would give the desired outputs as well but it is best practice to prevent overlapping to avoid unwanted results. In SQL, CASE statements can be aggregated and used within other statements as well (such as SELECT).

Leave a Reply