USING CASE IN SELECT STATEMENT

USING CASE IN SELECT STATEMENT/ IMPLEMENT IF-THEN-ELSE LOGIC IN SELECT STATEMENT

With Example let us understand, How to use CASE Condition in SQL SELECT Statement.

I have table EMP having column EMPLOYEEID, MARITALSTATUS, GENDER.

Click on pic to enlarge view

Say if we want to Display under following condition

•  If MaritalStatus is M, Then display it as Married, If MaritalStatus is S, then display it as Single and Other than M or S, then Display it as Unknown.

•  If Gender is M, Then display it as Male, If Gender is F, then display as Female and Other than M or F, then Display it as Unknown.

This can be achieved in many ways, for example using REPLACE Function etc, but since my intention is on CASE Statement, I will Continue to demonstrate it using CASE.

Let’s do it for Condition 1 first:

As per Condition 1, we can use CASE STATEMENT as Shown:

SELECT EMPLOYEEID

                    ,MARITALSTATUS

                    ,MARITALSTATUS_EXPN = CASE MARITALSTATUS

                                         WHEN 'M'

                                                             THEN 'MARRIED'

                                         WHEN 'S'

                                                             THEN 'SINGLE'

                                         ELSE 'UNKNOWN'

                                         END

FROM EMP

I’m using MARITALSTATUS_EXPN to display condition met column.

When MaritalStatus is M, then ‘Married’ is displayed and When MaritalStatus is S, ‘Single’ is displayed and anything other than this will be considered as UNKNOWN.

The result is of query is shown in below snippet:

Click on pic to enlarge view

Similarly, we can write CASE statement for Second condition as shown below:

SELECT EMPLOYEEID

      ,GENDER

      ,GENDER_EXPN = CASE GENDER

            WHEN 'M'

                  THEN 'MALE'

            WHEN 'F'

                  THEN 'FEMALE'

            ELSE 'UNKNOWN'

            END

FROM EMP

Click on pic to enlarge view

Combining both the expression above to get result in single Query,

SELECT EMPLOYEEID

        ,GENDER

        ,GENDER_EXPN = CASE GENDER

               WHEN 'M'

                       THEN 'MALE'

               WHEN 'F'

                       THEN 'FEMALE'

               ELSE 'UNKNOWN'

               END

        ,MARITALSTATUS_EXPN = CASE MARITALSTATUS

               WHEN 'M'

                       THEN 'MARRIED'

               WHEN 'S'

                       THEN 'SINGLE'

               ELSE 'UNKNOWN'

               END

FROM EMP

Click on pic to enlarge view