SQL CASE Statement

Next Page >>
SQL tutorial

SQL Tutorial » SQL CASE Statement

What is SQL case?

The SQL CASE statement goes through imposed conditions and returns a value when the first condition is met (similar to an if-then-else statement). So, once a condition is true, the reading will stop and return the result. If the conditions are not true, then return the value in the ELSE clause.

Note: If there is no ELSE part and no condition is true, return NULL.

Syntax:

CASE
WHEN condition11 THEN result11
WHEN condition12 THEN result12
.
.
WHEN condition1N THEN result1N
ELSE result
END;

Let's look at the "Order_details" table: We will have seven columns as: "id", "first_name", "last_name", "country", "city", "product_id"and "quantity":

ID	First Name	Last Name	Country	City	Product ID	Quantity
1	Clark	Kent	USA	Miami	24	11
2	Pire	Joveau	France	Paris	56	32
3	Nicky	Anderson		Boston	67	9
4	Pepe	Martínez	Spain	Madrid	112	26
5	Peter	Parker	SUA	Los Angeles	19	41

How do you write a case statement in SQL Server?

We have "Order_details" table: We will have five columns as: "id", "first_name", "last_name", "country" and "quantity":

The following SQL goes through conditions and returns a value when the first condition is met:

SELECT ID, Quantity,
CASE WHEN Quantity > 26 THEN 'The quantity is greater than 26'
WHEN Quantity = 26 THEN 'The quantity is 26'
ELSE 'The quantity is under 26'
END AS Quantity_text
FROM Order_details;

Display out: Number of Records: 5

ID	First Name	Last Name	Quantity	Quantity_text
1	Clark	Kent	11	The quantity is under 26
2	Pire	Joveau	32	The quantity is greater than 26
3	Nicky	Anderson	9	The quantity is under 26
4	Pepe	Martínez	26	The quantity is 26
5	Peter	Parker	41	The quantity is greater than 26

Tags: sql case, when, when null, in where clause, sensitive, when wxists, when multiple conditions, in select,
statement in where clause, insensitive,
What is SQL case?
How do you write a case statement in SQL Server?
How does case work?
How do you write multiple cases in SQL?

How does SQL CASE work?

The following SQL CASE will order the customers by City from Order_details DataBase. If in DataBase we do not have a record by City or City is NULL, then order by Country:

SELECT first_name, last_name, country, city,
FROM Order_details
ORDER BY
(CASE
WHEN city IS NULL THENcountry
ELSE city
END);

ID	First Name	Last Name	Country	City
3	Nicky	Anderson		Boston
5	Peter	Parker	SUA	Los Angeles
4	Pepe	Martínez	Spain	Madrid
1	Clark	Kent	USA	Miami
2	Pire	Joveau	France	Paris

SQL CASE in WHERE clause

Suppose we want to get all people from the Users table whose email is active or Not. To do this with CASE you could write:

SELECT first_name, last_name, email
FROM Users
WHERE 1 = CASE
WHEN email = 'active' THEN 1
ELSE 0
END

In this example CASE returns a one if email matches. Then, since 1 = 1 is true, email is active and the row is returned.

sql case, when, when null, in where clause, sensitive, when wxists, when multiple conditions, in select,
statement in where clause, insensitive,
What is SQL case?
How do you write a case statement in SQL Server?
How does case work?
How do you write multiple cases in SQL?

SQL CASE Statement - sql tutorial

Online Editor

ONLINE EDITOR

news templates

COLOR PICKER

news templates

This tool makes it easy to create, adjust, and experiment with custom colors for the web.

HTML Templates

Magnews2 is a modern and creative free magazine and news website template that will help you kick off your online project in style.

CSS HTML Layout

Find here examples of creative and unique website layouts.

Free CSS HTML Menu

Find here examples of creative and unique website CSS HTML menu.