MySQL Fetch different columns conditionally using MySQL CASE

Fetch different columns conditionally using MySQL CASE

Before using MySQL CASE to fetch different columns based on condition, i.e. using where clause to select different column. Firstly, Lets take a look at the definition of CASE statement.

MySQL CASE is simply a sequence of one or more IF else conditions.

The CASE statement is set of conditions and return a value when a certain condition is true (like an IF-THEN-ELSE statement). So, once a condition is true, it stops reading further cases and return the result.

If none of the conditions are true, it will return the value of the ELSE clause.

If there is no ELSE clause and none of the conditions are true, it returns NULL.

CASE Statement Syntax:

Syntax Explanation:

CASE starts with CASE followed by n number of combinations of WHEN, THEN sequence as we do in if(){}else if(){}else{}…..else{}.
ELSE is placed in the last for making a default condition to run and is completely optional.

If any of the conditions given are not found true then ELSE case is executed if present, if else case is omitted then results NULL.

Table Used in further queries:

Please execute the following queries for creating table I have used in the following examples.

Query to create table.
Query to insert data:

The table we will be revering to looks like:

Table to be referred in examples below

Examples of MySQL CASE:

1. Select different values based on column values
Result of above query

Here i haven’t used ELSE case which results NULL in type when both cases are false.
Now Running same query with ELSE case

Result of above query
2. Select columns based on condition
Result of above query

Similarly any of the columns can be fetched based on condition and requirement. One column can be fetched in one case, however if you want to fetch more than one column then either you can use CASE statements multiple times or use concatenation functions as I demonstrated above.

Share your thoughts

avatar
  Subscribe  
Notify of