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

0:00
Common table expressions are commonly used to refactor subqueries and
0:05
create more readable SQL.
0:06
A subquery, also called an inner query or
0:09
nested query, is just another result set generated using the SELECT keyword.
0:15
Subqueries can be used within a WHERE clause to filter results
0:18
based on information from another query.
0:21
For example, in this code, the subquery finds all car IDs from the cars table
0:26
where a car's model year is 2015, in other words, it returns a list of different IDs.
0:32
The WHERE clause then uses that list to further filter
0:36
the sales data based on cars whose ID match the subquery.
0:41
Subqueries are also used to create what are called derived tables, and
0:45
are often used in a FROM clause.
0:47
For example, this code uses two subqueries,
0:51
the first creates a result set that acts just like a table.
0:54
It has an alias name of all orders and
0:57
is then joined to another subquery, which also acts like a table named, late orders.
1:03
Using subqueries like this is useful,
1:05
since it lets you organize your data into sets that are more useful for
1:09
your data needs, but which aren't modeled in the actual schema of your database.
1:14
The problem with this approach,
1:15
however, is that the logic is buried midway through the query.
1:19
It's kind of like reading a story in which the main characters don't appear until
1:23
halfway through the book.
1:25
A neater approach is to identify those derived tables
1:28
at the beginning of your code.
1:30
It's like introducing the main characters of your story in chapter one.
1:34
Common table expressions let you do that, let me show you how.
1:38
If you'd like to follow along, click the SQL Playground button on this page, and
1:42
here's the code we just looked at.
1:44
When I run it I get a list of employee IDs, names,
1:49
a count of total orders, and a count of late orders for each of the employees.
1:54
As I mentioned before, CTEs let you think more clearly about the data your after.
1:59
Looking at the results here, I could say to myself, I need a list of all
2:03
the orders, I also need a list of just the orders that were late.
2:07
And finally, I need to match that data with my employees.
2:11
Let's start by creating that first set, all orders,
2:15
by converting the first subquery into a CTE.
2:18
I'll just copy the subquery, and go to a new tab here,
2:24
paste it in and convert it to a CTE.
2:28
So I'll just say WITH all order AS,
2:39
And that's our first CTE.
2:41
You can have multiple commen table expressions in a query.
2:44
And since it's original code had two subqueries, we'll have two CTEs.
2:49
To add another CTE add a comma after the closing parenthesis of the first CTE,
2:54
don't forget that comma, or you'll get an error.
2:57
Also, don't use the WITH keyword a second time, you only need it once.
3:03
To add the name of the second CTE, I'll just type late,
3:10
orders AS, and add another set of parenthesis, and
3:15
then I go back to my subquery, Copy that query.
3:24
Now I want a list of employee IDs and names.
3:35
So I'll select the employee ID and
3:37
the employee last name from the employees table.
3:41
Now remember these CTEs I just created act just like tables,
3:46
so I can join them to my query just like any other table.
3:51
So I'm gonna join employees to all orders on the employee ID.
3:58
Then I'm gonna join late orders on employees IDs.
4:04
Now I want a little bit more information.
4:11
I want account of all the orders, And I want account of all the late orders.
4:23
When I run the query I get the same results.
4:26
Now this query is the same number of lines as the original code, but
4:30
using CTEs, the code is much easier to read and think about.
4:35
In the next video I'll show you how you can reference a CTE inside another common
4:39
table expression to create complex queries that combine data in interesting ways.
4:45
Feel free to close the SQL Playground, I'll have a new one for
4:48
you in the next video.

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

Common Table Expressions Using WITH

Convert a Subquery to a CTE

Example Code

Subquery Example

Rewritten with Common Table Expresssions

Sign in