Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Common Table Expressions Using WITH!
You have completed Common Table Expressions Using WITH!
Preview
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