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.