Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Convert a Subquery to a CTE

4:49 with Dave McFarland

Subqueries are powerful tools for writing complex SQL queries. Unfortunately, they can also lead to difficult-to-understand SQL. Learn how to make your SQL more readable and modular by converting subqueries to common table expressions.

Teacher's Notes
Video Transcript
Downloads

Example Code

Subquery Example

SELECT 
  all_orders.EmployeeID, 
  Employees.LastName,
  all_orders.order_count AS total_order_count, 
  late_orders.order_count AS late_order_count
  FROM (
    SELECT EmployeeID, COUNT(*) AS order_count
    FROM Orders
    GROUP BY EmployeeID
  ) all_orders
  JOIN (
    SELECT EmployeeID, COUNT(*) AS order_count
    FROM Orders
    WHERE RequiredDate <= ShippedDate
    GROUP BY EmployeeID 
  ) late_orders
  ON all_orders.EmployeeID = late_orders.employeeID
  JOIN Employees
  ON all_orders.EmployeeId = Employees.Id

Rewritten with Common Table Expresssions

WITH all_orders AS (
    SELECT EmployeeID, COUNT(*) AS order_count
    FROM Orders
    GROUP BY EmployeeID
),
  late_orders AS (
    SELECT EmployeeID, COUNT(*) AS order_count
    FROM Orders
    WHERE RequiredDate <= ShippedDate
    GROUP BY EmployeeID 
)
SELECT 
  Employees.ID, LastName, 
  all_orders.order_count AS total_order_count,
  late_orders.order_count AS late_order_count
FROM Employees
JOIN all_orders ON Employees.ID = all_orders.EmployeeID 
JOIN late_orders ON Employees.ID = late_orders.EmployeeID

Common table expressions are commonly used to refactor subqueries and 0:00

create more readable SQL. 0:05

A subquery, also called an inner query or 0:06

nested query, is just another result set generated using the SELECT keyword. 0:09

Subqueries can be used within a WHERE clause to filter results 0:15

based on information from another query. 0:18

For example, in this code, the subquery finds all car IDs from the cars table 0:21

where a car's model year is 2015, in other words, it returns a list of different IDs. 0:26

The WHERE clause then uses that list to further filter 0:32

the sales data based on cars whose ID match the subquery. 0:36

Subqueries are also used to create what are called derived tables, and 0:41

are often used in a FROM clause. 0:45

For example, this code uses two subqueries, 0:47

the first creates a result set that acts just like a table. 0:51

It has an alias name of all orders and 0:54

is then joined to another subquery, which also acts like a table named, late orders. 0:57

Using subqueries like this is useful, 1:03

since it lets you organize your data into sets that are more useful for 1:05

your data needs, but which aren't modeled in the actual schema of your database. 1:09

The problem with this approach, 1:14

however, is that the logic is buried midway through the query. 1:15

It's kind of like reading a story in which the main characters don't appear until 1:19

halfway through the book. 1:23

A neater approach is to identify those derived tables 1:25

at the beginning of your code. 1:28

It's like introducing the main characters of your story in chapter one. 1:30

Common table expressions let you do that, let me show you how. 1:34

If you'd like to follow along, click the SQL Playground button on this page, and 1:38

here's the code we just looked at. 1:42

When I run it I get a list of employee IDs, names, 1:44

a count of total orders, and a count of late orders for each of the employees. 1:49

As I mentioned before, CTEs let you think more clearly about the data your after. 1:54

Looking at the results here, I could say to myself, I need a list of all 1:59

the orders, I also need a list of just the orders that were late. 2:03

And finally, I need to match that data with my employees. 2:07

Let's start by creating that first set, all orders, 2:11

by converting the first subquery into a CTE. 2:15

I'll just copy the subquery, and go to a new tab here, 2:18

paste it in and convert it to a CTE. 2:24

So I'll just say WITH all order AS, 2:28

And that's our first CTE. 2:39

You can have multiple commen table expressions in a query. 2:41

And since it's original code had two subqueries, we'll have two CTEs. 2:44

To add another CTE add a comma after the closing parenthesis of the first CTE, 2:49

don't forget that comma, or you'll get an error. 2:54

Also, don't use the WITH keyword a second time, you only need it once. 2:57

To add the name of the second CTE, I'll just type late, 3:03

orders AS, and add another set of parenthesis, and 3:10

then I go back to my subquery, Copy that query. 3:15

Now I want a list of employee IDs and names. 3:24

So I'll select the employee ID and 3:35

the employee last name from the employees table. 3:37

Now remember these CTEs I just created act just like tables, 3:41

so I can join them to my query just like any other table. 3:46

So I'm gonna join employees to all orders on the employee ID. 3:51

Then I'm gonna join late orders on employees IDs. 3:58

Now I want a little bit more information. 4:04

I want account of all the orders, And I want account of all the late orders. 4:11

When I run the query I get the same results. 4:23

Now this query is the same number of lines as the original code, but 4:26

using CTEs, the code is much easier to read and think about. 4:30

In the next video I'll show you how you can reference a CTE inside another common 4:35

table expression to create complex queries that combine data in interesting ways. 4:39

Feel free to close the SQL Playground, I'll have a new one for 4:45

you in the next video. 4:48

You need to sign up for Treehouse in order to download course files.

Sign up