facebook google plus twitter
Webucator's Free SQL Tutorial

Lesson: Conditional Processing with CASE

Welcome to our free SQL tutorial. This tutorial is based on Webucator's Introduction to SQL Training course.

In this lesson you will learn how to use CASE to add conditional logic to your queries.

Lesson Goals

  • Learn to use the CASE function to display different values depending on the values of a column or columns.

Using CASE

CASE functions contain one or more WHEN clauses as shown below.

Syntax

-- OPTION 1
SELECT CASE column
			WHEN VALUE THEN RETURN_VALUE
			WHEN VALUE THEN RETURN_VALUE
			WHEN VALUE THEN RETURN_VALUE
			WHEN VALUE THEN RETURN_VALUE
			ELSE RETURN_VALUE
		END
	AS ColumnName
FROM table

-- OPTION 2
SELECT CASE
			WHEN EXPRESSION THEN RETURN_VALUE
			WHEN EXPRESSION THEN RETURN_VALUE
			WHEN EXPRESSION THEN RETURN_VALUE
			WHEN EXPRESSION THEN RETURN_VALUE
			ELSE RETURN_VALUE
		END
	AS ColumnName
FROM table

Code Sample:

Case/Demos/Case.sql
/*
Create a report showing the customer ID and company name,
employee id, firstname and lastname, and the order id
and a conditional column called "Shipped" that displays "On Time"
if the order was shipped on time and "Late" if the order was shipped late.
*/

SELECT c.CustomerID, c.CompanyName, e.EmployeeID, e.FirstName, e.LastName, OrderID,
	(CASE
		WHEN ShippedDate < RequiredDate
			THEN 'On Time'
			ELSE 'Late'
			END) AS Shipped
FROM Orders o
	JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
	JOIN Customers c ON (c.CustomerID = o.CustomerID)
ORDER BY Shipped;

The above SELECT statement will return the following results (not all rows shown).Case Shipments Results

Code Sample:

Case/Demos/Case-GroupBy.sql
/*
Create a report showing the employee firstname and lastname,
a "NumOrders" column with a count of the orders taken, and a
conditional column called "Shipped" that displays "On Time" if
the order shipped on time and "Late" if the order shipped late.
Group records by employee firstname and lastname and then by the
"Shipped" status. Order by employee lastname, then by firstname,
and then descending by number of orders.
*/

SELECT e.FirstName, e.LastName, COUNT(o.OrderID) As NumOrders,
	(CASE
		WHEN o.ShippedDate < o.RequiredDate
			THEN 'On Time'
			ELSE 'Late'
			END)
		AS Shipped
FROM Orders o
	JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
GROUP BY e.FirstName, e.LastName,
	(CASE
		WHEN o.ShippedDate < o.RequiredDate
			THEN 'On Time'
			ELSE 'Late'
			END)
ORDER BY e.LastName, e.FirstName, NumOrders DESC;

The above SELECT statement will return the following results.Case Shipments By Employee Results

Notice how the GROUP BY clause contains the same CASE statement that is in the SELECT clause. This is required because all non-aggregate columns in the SELECT clause must also be in the GROUP BY clause and the GROUP BY clause cannot contain aliases defined in the SELECT clause.

Working with CASE

Duration: 10 to 15 minutes.

In this exercise you will practice using CASE.

  1. Create a report that shows the company names and faxes for all customers. If the customer doesn't have a fax, the report should show "No Fax" in that field as shown below.Company Names and Faxes Report

Solution:

Case/Solutions/Case.sql
SELECT CompanyName,
	(CASE
		WHEN Fax IS NULL
			THEN 'No Fax'
			ELSE Fax
	END)
	AS Fax
FROM Customers;