SQL | Case Statement

Control statements form the heart of most languages since they control the execution of other sets of statements. These are found in SQL too, and should be exploited for uses such as query filtering and query optimization through careful selection of tuples that match our requirement. In this post, we explore the Case-Switch statement in SQL.
The CASE statement is SQL’s way of handling if/then logic.
There can be two valid ways of going about the case-switch statements.

  1. The first takes a variable called case_value and matches it with some statement_list.
    CASE case_value
        WHEN when_value THEN statement_list
        [WHEN when_value THEN statement_list] ...
        [ELSE statement_list]
  2. The second considers a search_condition instead of variable equality and executes the statement_list accordingly.

        WHEN search_condition THEN statement_list
        [WHEN search_condition THEN statement_list] ...
        [ELSE statement_list]
  3. Examples:

    Say we have a relation, Faculty.

    Faculty Table:

    FacultyID Name Department Gender
    001 Aakash CS M
    002 Sahil EC M
    003 John HSS M
    004 Shelley CS F
    005 Anannya CS F
    006 Sia HSS F

    Let’s say we would like to modify this table such that if the department name is ‘CS’, it gets modified to ‘Computer Science’, if it is ‘EC’ it gets modified to ‘Electronics and Communication’, and if it is ‘HSS’ it gets modified to ‘Humanities and Social Sciences’. This can be achieved using case statement.

    Sample Query:
    Consider a variable, department_name which is entered in the SQL code.

    CASE department_name
     WHEN 'CS'
      THEN UPDATE Faculty SET
      department='Computer Science';
     WHEN 'EC'
      THEN UPDATE Faculty SET
      department='Electronics and Communication';
     ELSE UPDATE Faculty SET
     department='Humanities and Social Sciences';


    The department name corresponding to the given input gets renamed.

    Consider another query which selects all the fields corresponding to the Faculty table. Since the values written in the Gender field are single character values (M/F), we would like to present them in a more readable format.

    SELECT FacultyID, Name, Department,
    CASE Gender
     WHEN'M' THEN 'Male'
     WHEN'F' THEN 'Female'
    FROM Faculty


    FacultyID Name Department Gender
    001 Aakash CS Male
    002 Sahil EC Male
    003 John HSS Male
    004 Shelley CS Female
    005 Anannya CS Female
    006 Sia HSS Female

    Consider yet another application of case-switch in SQL- custom sorting.

    CREATE PROCEDURE GetFaculty(@ColToSort varchar(150)) AS
    SELECT FacultyID, Name, Gender, Department
    FROM Customers
      CASE WHEN @ColToSort='Department' THEN Department
           WHEN @ColToSort='Name' THEN Name
           WHEN @ColToSort='Gender' THEN Gender
           ElSE FacultyID


    The output gets sorted according to the provided field.

    The above procedure (function) takes a variable of varchar data type as its argument, and on the basis of that, sorts the tuples in the Faculty table.

    Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

This article is attributed to GeeksforGeeks.org

You Might Also Like

leave a comment



load comments

Subscribe to Our Newsletter