Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
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.
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
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up