close
The Wayback Machine - https://web.archive.org/web/20241006112919/https://www.geeksforgeeks.org/sql-case-statement/
Open In App

SQL CASE Statement

Last Updated : 22 Aug, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

The CASE statement in SQL is a versatile conditional expression that enables us to incorporate conditional logic directly within our queries. It is commonly used to generate new columns based on certain conditions and provide custom values or control the output of our queries.

In this article, We will learn about the CASE Statement in SQL in detail by understanding various examples and so on.

CASE Statement in SQL

  • The CASE statement in SQL is a conditional expression that allows you to perform conditional logic within a query.
  • It is commonly used to create new columns based on conditional logic, provide custom values, or control query outputs based on certain conditions.
  • If no condition is true then the ELSE part will be executed. If there is no ELSE part then it returns NULL.

Syntax:

To use CASE Statement in SQL, use the following syntax:

CASE case_value
WHEN condition THEN result1
WHEN condition THEN result2
…
Else result
END CASE;

Example of SQL CASE Statement

Let’s look at some examples of the CASE statement in SQL to understand it better.

Let’s create a demo SQL table, which will be used in examples.

Demo SQL Database

We will be using this sample SQL table for our examples on SQL CASE statement:

CustomerID CustomerName LastName Country Age Phone
1 Shubham Thakur India 23 xxxxxxxxxx
2 Aman Chopra Australia 21 xxxxxxxxxx
3 Naveen Tulasi Sri Lanka 24 xxxxxxxxxx
4 Aditya Arpan Austria 21 xxxxxxxxxx
5 Nishant. Salchichas S.A. Jain Spain 22 xxxxxxxxxx

You can create the same Database in your system, by writing the following MySQL query:

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age int(2),
  Phone int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
       (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
       (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
       (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
       (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Example 1: Simple CASE Expression

In this example, we use CASE statement

Query:

SELECT CustomerName, Age,
CASE
    WHEN Country = "India" THEN 'Indian'
    ELSE 'Foreign'
END AS Nationality
FROM Customer;

Output:

CustomerName Age Nationality
Shubham 23 Indian
Aman 21 Foreign
Naveen 24 Foreign
Aditya 21 Foreign
Nishant. Salchichas S.A. 22 Foreign

Example 2: SQL CASE When Multiple Conditions

We can add multiple conditions in the CASE statement by using multiple WHEN clauses.

Query:

SELECT CustomerName, Age,
CASE
    WHEN Age> 22 THEN 'The Age is greater than 22'
    WHEN Age = 21 THEN 'The Age is 21'
    ELSE 'The Age is over 30'
END AS QuantityText
FROM Customer;

Output:

CustomerName Age QuantityText
Shubham 23 The Age is greater than 22
Aman 21 The Age is 21
Naveen 24 The Age is greater than 22
Aditya 21 The Age is 21
Nishant. Salchichas S.A. 22 The Age is over 30

Example 3: CASE Statement With ORDER BY Clause

Let’s take the Customer Table which contains CustomerID, CustomerName, LastName, Country, Age, and Phone. We can check the data of the Customer table by using the ORDER BY clause with the CASE statement.

Query:

SELECT CustomerName, Country
FROM Customer
ORDER BY
(CASE
    WHEN Country  IS 'India' THEN Country
    ELSE Age
END);

Output:

CustomerName Country
Aman Australia
Aditya Austria
Nishant. Salchichas S.A. Spain
Naveen Sri lanka
Shubham India

Important Points About CASE Statement

  • The SQL CASE statement is a conditional expression that allows for the execution of different queries based on specified conditions.
  • There should always be a SELECT in the CASE statement.
  • END ELSE is an optional component but WHEN THEN these cases must be included in the CASE statement.
  • We can make any conditional statement using any conditional operator (like WHERE ) between WHEN and THEN. This includes stringing together multiple conditional statements using AND and OR.
  • We can include multiple WHEN statements and an ELSE statement to counter with unaddressed conditions.

Conclusion

The CASE statement provides a robust mechanism for incorporating conditional logic in SQL queries. By using this statement, you can handle various conditions and customize the output of your queries effectively. Understanding how to implement CASE expressions allows you to perform more sophisticated data manipulation and reporting, making your SQL queries more dynamic and responsive to different scenarios.

FAQs

What is a CASE statement in SQL?

The CASE statement in SQL is a conditional expression that allows you to execute different logic based on certain conditions within a query. It is used to create new columns, customize values, or modify query outputs depending on specified criteria.

How to write a CASE statement?

To write a CASE statement, you need to specify conditions and corresponding results. You define different outcomes based on whether the conditions are met or not, with an optional default result if none of the conditions are true.

How to write a CASE statement in SQL with one condition?

To write a CASE statement with a single condition, you specify the condition to evaluate and provide the result if the condition is true. You can also include an alternative result if the condition is not met.



Previous Article
Next Article

Similar Reads

PL/SQL CASE Statement
PL/SQL stands for Procedural Language Extension to the Structured Query Language and it is designed specifically for Oracle databases it extends Structured Query Language (SQL) capabilities by allowing the creation of stored procedures, functions, and triggers. It is a block-structured language that combines SQL with the procedural features of prog
4 min read
What is the CASE statement in SQL Server with or condition?
In SQL Server, the CASE statement cannot directly support the use of logical operators like OR with its structure. Instead of CASE it operates based on the evaluation of multiple conditions using the WHEN keyword followed by specific conditions. In this article, we will learn about the OR is not supported with CASE Statement in SQL Server with a de
4 min read
How to do Case Sensitive and Case Insensitive Search in a Column in MySQL
LIKE Clause is used to perform case-insensitive searches in a column in MySQL and the COLLATE clause is used to perform case-sensitive searches in a column in MySQL. Learning both these techniques is important to understand search operations in MySQL. Case sensitivity can affect search queries in MySQL so knowing when to perform a specific type of
4 min read
Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
Structured Query Language (SQL): Structured Query Language (SQL) has a specific design motive for defining, accessing and changement of data. It is considered as non-procedural, In that case the important elements and its results are first specified without taking care of the how they are computed. It is implemented over the database which is drive
2 min read
Configure SQL Jobs in SQL Server using T-SQL
In this article, we will learn how to configure SQL jobs in SQL Server using T-SQL. Also, we will discuss the parameters of SQL jobs in SQL Server using T-SQL in detail. Let's discuss it one by one. Introduction :SQL Server Agent is a component used for database task automation. For Example, If we need to perform index maintenance on Production ser
7 min read
How to find all Employee records containing a Specific Word regardless case-sensitive in SQL
In this article, we will cover how to find all Employee records containing a Specific Word regardless of case-sensitive in SQL and will discuss steps by steps. Introduction :We will discuss, how to find all employee records containing a specific word regardless of the case-sensitive. We create the database employee and check for the following query
2 min read
SQL Server CASE Expression
The CASE expression is used to show another column which can be evaluated based on the conditions depending on the existing columns. The CASE expression consists of WHEN and THEN statements. WHEN is used when the condition satisfies and THEN executes the operation or assignment of value of that column based on the evaluation of the expression. The
6 min read
Using CASE in ORDER BY Clause to Sort Records By Lowest Value of 2 Columns in SQL
The CASE statement in SQL is a versatile tool for implementing conditional logic within queries, similar to IF-THEN-ELSE constructs in programming languages. The ORDER BY clause in SQL is used to sort the results of a query based on one or more columns, enhancing data readability and analysis. In this guide, we'll explore how to use the CASE statem
3 min read
Reverse Statement Word by Word in SQL server
To reverse any statement Word by Word in SQL server we could use the SUBSTRING function which allows us to extract and display the part of a string. Pre-requisite :SUBSTRING function Approach : Declared three variables (@Input, @Output, @Length) using the DECLARE statement. Use the WHILE Loop to iterate every character present in the @Input. For th
2 min read
How to Update Multiple Columns in Single Update Statement in SQL?
In this article, we will see, how to update multiple columns in a single statement in SQL. We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required. UPDATE for Multiple ColumnsSyntax: U
3 min read
Multi-Statement Table Valued Function in SQL Server
In SQL Server, a multi-statement table-valued function (TVF) is a user-defined function that returns a table of rows and columns. Unlike a scalar function, which returns a single value, a TVF can return multiple rows and columns. Multi-statement function is very much similar to inline functions only difference is that in multi-statement function we
4 min read
SQL Server MERGE Statement
MERGE is a powerful SQL statement that allows you to perform INSERT/UPDATE/DELETE operations depending on the conditions you specify. It makes it easy to synchronize data between source and destination tables. This makes it efficient to handle data changes in one single statement. MERGE makes it easier to manage complex operations such as data ware
6 min read
PL/SQL GOTO Statement
PL/SQL also known as Procedural Language/Structured Query Language, PL/SQL is a powerful programming language used in Oracle databases to do interaction with data. One of its features is the GOTO statement, which helps control how a program flows by allowing it to jump to specific statements within the same part of the program. What is the GOTO Sta
5 min read
PL/SQL NULL Statement
PL/SQL, the Procedural Language/Structured Query Language, is a database programming language utilized for database management in Oracle. Within this language, the Null statement plays a crucial role in enhancing code readability and functionality. This article aims to delve into the significance of the Null statement in PL/SQL, exploring its vario
5 min read
PL/SQL CONTINUE Statement
PL/SQL is a block-structured language that enables developers to combine the power of SQL with procedural statements. All the statements of a block are passed to the Oracle engine all at once which increases processing speed and decreases the traffic. CONTINUE StatementIn PL/SQL (Procedural Language/Structured Query Language), the CONTINUE statemen
4 min read
SQL | INSERT IGNORE Statement
We know that a primary key of a table cannot be duplicated. For instance, the roll number of a student in the student table must always be distinct. Similarly, the EmployeeID is expected to be unique in an employee table. When we try to insert a tuple into a table where the primary key is repeated, it results in an error. However, with the INSERT I
2 min read
SQL | DESCRIBE Statement
Prerequisite: SQL Create Clause As the name suggests, DESCRIBE is used to describe something. Since in a database, we have tables, that's why do we use DESCRIBE or DESC(both are the same) commands to describe the structure of a table. Syntax: DESCRIBE one; OR DESC one; Note: We can use either DESCRIBE or DESC(both are Case Insensitive). Suppose our
2 min read
SQL INSERT INTO SELECT Statement
In SQL, the INSERT INTO statement is used to add or insert records to the specified table. We can use this statement to add data directly to the table. We use the VALUES keyword along with the INSERT INTO statement. VALUES keyword is accompanied by column names in a specific order in which we want to insert values in them. SELECT statement is used
5 min read
SQL Server SELECT INTO Statement
SQL Server is a relational database management system. SQL Server offers robust security features to protect data integrity and confidentiality. It includes authentication, authorization, encryption, and various mechanisms to secure the database environment. It is designed to scale from small applications to large, enterprise-level databases. It pr
6 min read
What is Nested Select Statement in PL/SQL?
PL/SQL is a Procedural Language/Structured Query Language and it enables users to write procedural logic directly within the database, including procedures, functions, triggers, and packages. In this article, we will understand Nested select statements in PL/SQL along with the examples and so on. Understanding Nested Select Statement in PL/SQLThe n
4 min read
What is Nested Select Statement in SQL Server
SQL Server is a powerful relational database management system. Sql Server is very good with its robustness and scalability. SQL Server operates as a client-server structure, imparting centralized management and scalability for huge-scale applications and enterprise-stage solutions. It offers advanced features which include excessive availability,
3 min read
PL/SQL Statement level Triggers
Statement-level triggers in Oracle databases execute actions for each transaction, responding to various database events like DML and DDL statements, system events, and user interactions. They act as programmed responses to specific table events, enhancing database management and automation. Stored as named PL/SQL blocks, triggers execute automatic
5 min read
SQL CREATE VIEW Statement
The SQL CREATE VIEW statement is a very powerful feature in RDBMSs that allows users to create virtual tables based on the result set of a SQL query. Unlike regular tables, these views do not store data themselves rather they provide a way of dynamically retrieving and presenting data from one or many underlying tables. Views help simplify complex
4 min read
PL/SQL INSERT Statement
The PL/SQL INSERT statement is vital for adding new records to a database table. By specifying the table's name and providing values for its columns, users can populate their database with essential information. This functionality enables efficient data entry and ensures the completeness of datasets, facilitating various database operations and ana
3 min read
How to Combine LIKE and IN in SQL Statement
Combining 'LIKE' and 'IN' operators in SQL can greatly enhance users' ability to retrieve specific data from a database. By Learning how to use these operators together users can perform more complex queries and efficiently filter data based on multiple criteria, making queries more precise and targeted. This article explores how the LIKE and IN op
4 min read
SQL MERGE Statement
SQL MERGE Statement combines INSERT, DELETE, and UPDATE statements into one single query. MERGE Statement in SQLMERGE statement in SQL is used to perform insert, update, and delete operations on a target table based on the results of JOIN with a source table. This allows users to synchronize two tables by performing operations on one table based on
4 min read
PL/SQL CREATE TABLE Statement
PL/SQL CREATE TABLE statement is a fundamental aspect of database design and allows users to define the structure of new tables, including columns, data types and constraints. This statement is crucial in organizing data effectively within a database and providing a blueprint for how data should be structured. In this article, we will explore the s
4 min read
How to Use PL SQL Insert, Update, Delete And Select Statement?
PL/SQL is a powerful extension of SQL specifically designed for Oracle databases. It enables developers to create procedural logic and execute SQL commands within Oracle database environments. In this article, we will explore the usage of fundamental PL/SQL statements such as INSERT, UPDATE, DELETE and SELECT with he help of various examples which
6 min read
SQL SELECT INTO Statement
The SQL SELECT INTO statement is used to copy data from one table into a new table. Note: The queries are executed in SQL Server, and they may not work in many online SQL editors so better use an offline editor. SyntaxSQL INSERT INTO Syntax is: SELECT column1, column2... INTO NEW_TABLE from SOURCE_TABLEWHERE Condition; To copy the entire data of th
3 min read
SQL DROP INDEX Statement
The SQL DROP INDEX statement removes an existing Index from a database table. SQL DROP INDEXThe SQL DROP INDEX Command is used to remove an index from the table. Indexes occupy space, which can cause extra time consumption on table modification operations. Benefits of Using DROP INDEX:Removing unused indexes can improve INSERT, UPDATE, and DELETE o
3 min read