![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQL - CASE
.
The SQL CASE statement is a conditional statement that helps us to make decisions based on certain conditions. It evaluates the set of conditions and returns the respective when a condition is satisfied.
The CASE statement works like a simplified IF-THEN-ELSE statement and allows for multiple conditions to be tested.
This starts with the keyword CASE followed by multiple conditionals statements. Each conditional statement consists of at least one pair of WHEN and THEN statements. Where WHEN specifies conditional statements and THEN specifies the actions to be taken.
It is often used to create a new column with values based on the value of an existing column.
Let us look at a simple scenario to understand this statement. Suppose we are analyzing the data; we often wish to define rules based on certain conditions.
For e.g. when the credit limit of a customer is above ‘10,000’, then the customer will be recognized as a ‘High value customer’; when the credit limit is above ‘5000’, then the customer will be recognized as a ‘Mid value customer’; otherwise the customer will be recognized as the ‘Low value customer’ as shown in the table below −
![CASE](https://www.tutorialspoint.com/sql/images/case.jpg)
Syntax
Following is the syntax of SQL CASE statement −
CASE WHEN condition1 THEN statement1, WHEN condition2 THEN statement2, WHEN condition THEN statementN ELSE result END;
Where, condition1, condition2, etc. are the conditional statements and statement1, statement2, etc.. are the actions to be taken when the condition is true.
Once the condition is met, the CASE statement will stop verifying further and it will return the result.
If none of the conditions are met (TRUE), then it returns the value mentioned in the ELSE clause.
It returns NULL if the ELSE part is not mentioned and also none of the conditions are TRUE.
Example
Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. using the following query −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Now, insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
The table will be created as follows −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
In the following query, we are using multiple WHEN and THEN conditions to the CASE statement along with the ELSE clause.
If the age of the customer is greater than 30, it returns Gen X otherwise moves to the further WHEN and THEN conditions. If none of the conditions is matched with the CUSTOMERS table, CASE returns the 'Gen Alpha’ value as mentioned in the ELSE part in the query as shown below −
SELECT NAME, AGE, CASE WHEN AGE > 30 THEN 'Gen X' WHEN AGE > 25 THEN 'Gen Y' WHEN AGE > 22 THEN 'Gen Z' ELSE 'Gen Alpha' END AS Generation FROM CUSTOMERS
Output
The output produced is as follows −
+----------+-----+------------+ | NAME | AGE | Generation | +----------+-----+------------+ | Ramesh | 32 | Gen X | | Khilan | 25 | Gen Z | | kaushik | 23 | Gen Z | | Chaitali | 25 | Gen Z | | Hardik | 27 | Gen Y | | Komal | 22 | Gen Alpha | | Muffy | 24 | Gen Z | +----------+-----+------------+
Example
Let's take a look at another query where we want to provide a 25% increment to each customer if the amount is less than 4500 from the CUSTOMERS table previously created −
SELECT *, CASE WHEN SALARY < 4500 THEN (SALARY + SALARY * 25/100) END AS INCREMENT FROM CUSTOMERS;
Output
Here, the SQL command checks if the salary is less than 4500. If this condition is satisfied, a new column ‘INCREMENT’ will contain the values that is equal to salary with 25% of increment.
Since the ELSE part is not mentioned in the above query and none of the conditions are true for few customers, NULL is returned which shows that they didn’t get any increment as shown below −
+----+----------+-----+-----------+----------+-------------+ | ID | NAME | AGE | ADDRESS | SALARY | INCREMENT | +----+----------+-----+-----------+----------+-------------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | 2500.000000 | | 2 | Khilan | 25 | Delhi | 1500.00 | 1875.000000 | | 3 | kaushik | 23 | Kota | 2000.00 | 2500.000000 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | NULL | | 5 | Hardik | 27 | Bhopal | 8500.00 | NULL | | 6 | Komal | 22 | MP | 4500.00 | NULL | | 7 | Muffy | 24 | Indore | 10000.00 | NULL | +----+----------+-----+-----------+----------+-------------+
CASE statement with ORDER BY clause
We can use CASE statement with ORDER BY clause. The ORDER BY clause in SQL sorts the result in ascending (default) or descending order.
Example
In this query, the CASE statement is used to sort the results based on either the ‘NAME’ column or the ‘ADDRESS’ column, depending on the value of the ‘NAME’ column. If the ‘NAME’ column starts with 'K', the results are sorted by the ‘NAME’ column; otherwise, the results are sorted by the ‘ADDRESS’ column −
SELECT * FROM CUSTOMERS ORDER BY (CASE WHEN NAME LIKE 'k%' THEN NAME ELSE ADDRESS END);
Output
The result obtained by executing the above query is as shown below −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | +----+----------+-----+-----------+----------+
CASE statement with GROUP BY clause
We can also use the CASE statement with GROUP BY clause. The GROUP BY clause in SQL groups the rows that have same values within one or more columns where an aggregate function is applied to produce summaries.
Example
In the following query we are trying to group the customers based on their salaries and calculate the sum of the salary for a specified range of customer data.
If the value in ‘SALARY is less than or equal to 4000, the data will be grouped as ‘Lowest paid’. If the value is greater than 4000 and less than or equal to 6500, it will be grouped as ‘Average paid’. All other values will be grouped as ‘Highest paid’. The SUM function is used to calculate the total of the SALARY for each group −
SELECT CASE WHEN SALARY <= 4000 THEN 'Lowest paid' WHEN SALARY > 4000 AND SALARY <= 6500 THEN 'Average paid' ELSE 'Highest paid' END AS SALARY_STATUS, SUM(SALARY) AS Total FROM CUSTOMERS GROUP BY CASE WHEN SALARY <= 4000 THEN 'Lowest paid' WHEN SALARY > 4000 AND SALARY <= 6500 THEN 'Average paid' ELSE 'Highest paid' END
Output
Following is the output of the above query −
+---------------+----------+ | SALARY_STATUS | Total | +---------------+----------+ | Lowest paid | 5500.00 | | Average paid | 11000.00 | | Highest paid | 18500.00 | +---------------+----------+
CASE statement with WHERE clause
We can use the CASE statement with the WHERE clause as well. The WHERE clause is used to filter the rows in a table based on a specified condition.
Example
In the following query, the CASE statement is used to return the different designations of the customers based on their age. The WHERE clause is used to filter the rows based on the salary of the customers −
SELECT NAME, ADDRESS, CASE WHEN AGE < 25 THEN 'Intern' WHEN AGE >= 25 and AGE <= 27 THEN 'Associate Engineer' ELSE 'Senior Developer' END as Designation FROM CUSTOMERS WHERE SALARY >= 2000
Output
Output of the above query is as follows −
+----------+-----------+--------------------+ | NAME | ADDRESS | Designation | +----------+-----------+--------------------+ | Ramesh | Ahmedabad | Senior Developer | | kaushik | Kota | Intern | | Chaitali | Mumbai | Associate Engineer | | Hardik | Bhopal | Associate Engineer | | Komal | MP | Intern | | Muffy | Indore | Intern | +----------+-----------+--------------------+
CASE statement with UPDATE statement
We can use CASE statement within the UPDATE statement to perform conditional updates on data in a table.
Example
In the following query we are trying to update the salary of all the customers based on their age.
If the age of the customer is equal to ’25’, their salary will be updated to ‘17000’. If the age is equal to ‘32’, it will be updated to ‘25000’. For the customers with other ages, salaries will be updated to ‘12000’ −
UPDATE CUSTOMERS SET SALARY= CASE AGE WHEN 25 THEN 17000 WHEN 32 THEN 25000 ELSE 12000 END;
Output
We get the following result. We can observe that the changes have been done in 7 rows −
(7 rows affected)
Verification
We can rectify the changes done in the CUSTOMERS table using the below query −
Select * from customers;
The table is displayed as follows −
+----+----------+-----+-----------+-----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+-----------+ | 1 | Ramesh | 32 | Ahmedabad | 25000.00 | | 2 | Khilan | 25 | Delhi | 17000.00 | | 3 | kaushik | 23 | Kota | 12000.00 | | 4 | Chaitali | 25 | Mumbai | 17000.00 | | 5 | Hardik | 27 | Bhopal | 12000.00 | | 6 | Komal | 22 | MP | 12000.00 | | 7 | Muffy | 24 | Indore | 12000.00 | +----+----------+-----+-----------+-----------+
As we can see in the above table, the SALARY of all the customers has been updated corresponding to their age.
CASE statement with INSERT statement
We can also insert the data into SQL tables with the help of CASE statement. In SQL, we need to specify variables to store column values on the basis of which we need to insert the data in CASE statement.
Example
Here, we have declared the variable ‘AGE’ with the value ‘28’ to store the age of the customer. Then the ‘AGE’ variable acts as the criteria for the salary of the customer. If the age of the customer is greater than or equal to 25, then the salary will be 23000; otherwise the salary will be 14000 −
Declare @AGE INT Set @AGE = 28 INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (10, 'Viren', 28, 'Varanasi', CASE WHEN @AGE >= 25 THEN 23000 ELSE 14000 END);
Output
We get the following result. We can observe that the change has been done in 1 row −
(1 row affected)
Verification
We can rectify the changes done in the CUSTOMERS table using the below query −
Select * from customers;
The table is displayed as follows −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | | 10 | Viren | 28 | Varanasi | 23000.00 | +----+----------+-----+-----------+----------+