AgerNic.com
WEB DEVELOPER SITE, HTML, CSS, PHP, SQL

SQL CASE Statement


<< Previous Page
SQL tutorial
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

Related subjects:
SQL order by SQL where clause PHP select / view data Conect to MySQLi SQL insert into table

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
news 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
news templates
Find here examples of creative and unique website layouts.


Free CSS HTML Menu
news templates
Find here examples of creative and unique website CSS HTML menu.


Home - Database
SQL - Database
SQL Syntax
SQL Select Data
SQL Insert Data
SQL Update
SQL Delete Row
PHP mysqli_query()
SQL order
SQL Create Database
SQL Display Data
SQL Count()
SQL mysqli_connect
SQL create table
SQL Where And & Or
SQL like Syntax
SQL Between Operator
SQL IN Operator
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
news 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
news templates
Find here examples of creative and unique website layouts.


Free CSS HTML Menu
news templates
Find here examples of creative and unique website CSS HTML menu.


jQuery replace...
jQuery replaceClass...
jQuery click ()...
jQuery switchClass()...
jQuery draggable()...
jQuery HTML...
jQuery scroll smooth...
jQuery scroll()...
jQuery stop()...
jQuery animate()...
jQuery SlideToggle()...
jQuery slideDown()...
jQuery slideUp()...
jQuery fadeToggle...
jQuery fadeOut()...
jQuery fadeIn()...
jQuery Effects - hide () and show ()...
jQuery Events...
jQuery Attributes...
jQuery Selectors...