Sql Case Operator


The SQL CASE is used to provide if-then-else type of logic to SQL. It is used to control the execution of other sets of statements.

The SQL CASE statement goes through conditions and return a value or result when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading next condition and return the result or value. If no conditions are true, it returns the value or data in the ELSE clause.

If there is no ELSE statement (is optional) part and no conditions are true, then it returns NULL value.

The SQL CASE can be used in any valid sql statement or expression.


Search Keys

  • sql case when exists
  • sql case when
  • sql case when null
  • sql case statement in where clause
  • sql case sensitive
  • sql case sensitive or not
  • sql case when then multiple
  • sql case in where clause
  • sql case study
  • sql case insensitive compare
  • sql case when then
  • sql case when null then 0
  • sql case with multiple conditions

Sql Case Syntax

The below syntax is used to create case statements.


CASE 
    WHEN condition_1 THEN value_1_or_result_1
    [WHEN condition_2 THEN value_2_or_result_2]
    [ELSE value_or_result]
END CASE

Note:-
  • You can provide any valid sql expression in the condition section.

Sample Database Table - Student

ID SName Gender Dept AvgMarks
1 Varshini F CS 78
2 Rishi M IT 65
3 Kumar M CS 34
4 Vidya F CS 55
5 Devi F IT 23
6 Siva M IT 88

SQL Case Example

The following SQL statement goes through conditions on "gender" column and returns a value when the first condition is met:


SELECT ID, SName, 
CASE 
WHEN Gender ='F' THEN 'Female'
WHEN Gender ='M' THEN 'Male'
END AS NewGender
FROM Student

In the above query, we’ve included the CASE statement with the "gender" column itself. You can check each row to see whether "gender" meets the condition "gender" = 'F' or "gender" = 'M' and then see the result in the column generated using the CASE statement.

The result of above query is:

ID SName NewGender
1 Varshini Female
2 Rishi Male
3 Kumar Male
4 Vidya Female
5 Devi Female
6 Siva Male

The following SQL statement goes through conditions on "AvgMarks" column and returns a value when the first condition is met:


SELECT ID, SName, Gender, 
CASE 
WHEN AvgMarks > 80 THEN 'A Grade'
WHEN AvgMarks > 60 AND AvgMarks <= 80 THEN 'B Grade'
WHEN AvgMarks > 40 AND AvgMarks <= 60 THEN 'C Grade'
ELSE 'No Grade' 
END AS Grade 
FROM Student

In the above query, we’ve included the CASE statement with ELSE section.

In the above example, if the value in the "avgmarks" column of a given row is "65", it will produce a result of "B Grade".
Here’s what happens if the value in the "avgmarks" column is "58", SQL will do the following:

  1. Check to see if average is greater than 80. "58" is not greater than 80, so move on to the next WHEN/THEN
  2. Check to see if average is between 60 and 80. "58" is not between 60 and 80, so move on to the next WHEN/THEN
  3. Check to see if average is between 40 and 60. "58" is between 40 and 60, so record "C Grade" in the "Grade" column.

The result of above query is:

ID SName Gender Grade
1 Varshini F B Grade
2 Rishi M B Grade
3 Kumar M No Grade
4 Vidya F C Grade
5 Devi F No Grade
6 Siva M A Grade

Search Keys

  • sql case with join
  • sql case when or
  • sql case when null or empty
  • sql case when not null
  • sql case multiple then
  • sql case multiple conditions
  • sql case mysql
  • sql case multiple columns
  • sql case multiple values
  • sql case max
  • sql case multiple matches
  • sql case multiple fields
  • sql case max date
  • sql case multiple then values