SQL CASE : Explanation and Syntax
The SQL CASE statement works similar to the SWITCH-CASE statement or the IF-THEN-ELSE statement used in many programming languages. The SQL CASE statement is used to select data and provide output based on the conditions inside the CASE statement. Let us understand this better with the real world examples given below.
SQL CASE Statement – Syntax:
The syntax for using the SQL CASE statement is:
SELECT columns, CASE WHEN condition THEN operation1 ELSE operation2 END AS column_name FROM table_name;
SELECT columns – Use the SELECT keyword, followed by the names of the columns that you want to output.
CASE – This shows the start of the CASE statement.
WHEN condition THEN operation1 – The WHEN keyword is followed by a condition. If the condition is true, operation1 is executed. The THEN keyword is followed by the operation that is to be performed.
More than one WHEN condition THEN operation steps can be performed.
It is always a good practice to use the ELSE operation. This acts like the default case used in the SWITCH CASE statements.
END – Ends the CASE statement.
AS column_name – The column_name is the name given to the column where the result of the conditional statement is stored.
FROM table_name – The FROM keyword is followed by the table_name. The table_name is the name of the table from which the data to be operated upon is selected.
SQL CASE Statement – Real World Examples:
To understand the CASE statement, first let us create a database and a table in it; populate the table (fill the table with data) and then operate on it.
1) Create a database with any database name. In this example, the database is named hub. Write the following code in any SQL console. I am using a MySQL console.
CREATE DATABASE hub;
2) In order to create a table in that database, first we have to use the database. To use the database enter the following code.
3) Now create a table called ‘friends’ with four fields namely id, first_name, last_name and age. Enter the following code to create the table.
CREATE TABLE `hub`.`friends` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 30 ) NOT NULL ,
`last_name` VARCHAR( 30 ) NOT NULL ,
`age` INT( 3 ) NOT NULL
) ENGINE = MYISAM ;
4) Populate the table using the INSERT INTO statement.
INSERT INTO `hub`.`friends` (`id`, `first_name`, `last_name`, `age`) VALUES (NULL, ‘harry’, ‘potter’, ’20’), (NULL, ‘ron’, ‘weasley’, ’20’), (NULL, ‘hermione’, ‘granger’, ’20’), (NULL, ‘Severus’, ‘Snape’, ’40’), (NULL, ‘Minerva’, ‘McGonaggal’, ’60’), (NULL, ‘albus’, ‘dumbledore’, ’70’);
5) Now use the SQL CASE statement and perform a few operations.
In the following example, the SQL CASE statement is used to display the first name and last name of each entity in the table called friends. The CASE statement is used to create a condition that displays the designation of the entity as
1) ‘Student’ if the age is less than 21,
2) ‘Professor’ if the age is less than 70,
3) ‘Headmaster’ if the age is otherwise (meaning, if the age doesn’t pass either conditions)
The END AS ‘Designation’ is used to display the results in a column called Designation.
Note that the column Designation is not present in the table nor is the data displayed in Designation stored in the table (the name of the table that we are using is friends).
mysql> SELECT first_name, last_name, CASE WHEN age < 21 THEN ‘Student’ WHEN age < 70 THEN ‘Professor’ ELSE ‘Headmaster’ END AS ‘Designation’ FROM friends;
SQL CASE: Pitfalls:
One should be very careful about where to use the (‘) symbol and where not to use them. The use of spaces is very very important.
If you’re looking to learn SQL, why don’t you check out some of my other SQL basics tutorials?