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
Learn the basics of common table expressions in SQL: what they are, why they're useful, and the basics of how to create them.
Example Code
Basic Common Table Expression
WITH product_details AS (
SELECT ProductName, CategoryName, UnitPrice, UnitsInStock
FROM Products
JOIN Categories ON PRODUCTS.CategoryId = Categories.Id
WHERE Products.Discontinued = 0
)
SELECT * FROM product_details
ORDER BY 2, 1
Basic Common Table Expression Expanded
WITH product_details AS (
SELECT ProductName, CategoryName, UnitPrice, UnitsInStock
FROM Products
JOIN Categories ON PRODUCTS.CategoryId = Categories.Id
WHERE Products.Discontinued = 0
)
SELECT CategoryName, COUNT(*) AS unique_product_count, SUM(UnitsInStock) AS stock_count
FROM product_details
GROUP BY 1
ORDER BY 2
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
[MUSIC]
0:00
[SOUND] Writing SQL to discover the
information stored in a database is often
0:05
fun and rewarding.
0:09
It can also sometimes feel like you're
working your way down a rabbit hole.
0:10
Creating complicated queries, joining
multiple tables, and building complex
0:14
reports with aggregate data often
requires a lot of crazy looking SQL.
0:19
What's worse is having to dig your way
through the confusing logic of someone
0:24
else's query, that includes nested
sub queries and lots of joins.
0:29
In this workshop,
0:34
I'll teach you how to make your queries
easier to read, easier to think about and
0:35
easier to build using what's called
a Common Table Expression or CTE.
0:39
Even better,
CTEs are really simple to learn.
0:44
Most popular databases support CTEs,
like Oracle, SQL Server,
0:48
PostgreSQL, Redshift, SQLite,
and MySQL version 8 and later.
0:52
A common table expression is nothing
more than an SQL query that you name and
0:58
reuse within a longer query.
1:03
It's a temporary result set.
1:05
Think of it as a temporary table.
1:07
Creating a common table
expression is easy.
1:09
You place a CTE at the beginning of
a complete query using a simple syntax.
1:11
Begin with the keyword WITH,
followed by a name, the keyword AS, and
1:16
a SELECT statement inside of parenthesis.
1:20
The CTE name is like an alias for
the results returned by the query.
1:23
You can then use the name just like a
table name in queries that follow the CTE.
1:28
Lets look at a simple example.
1:33
If you'd like to follow along click
the SQL playground button on this page
1:36
to open our online SQL tool.
1:40
I'll start with a simple query to
retrieve data from a business database
1:43
containing product and sales information.
1:47
This query combines data from two tables.
1:49
I'll select a product name,
a category name, a unit price,
1:53
and the number of units that we have in
stock for that from the products table.
1:59
And then I join that to categories.
2:04
And I'll simply match up the category ID,
2:07
which is in the products table,
to the ID in the categories table.
2:09
I also want to make sure that I'm only
looking for products that we still sell.
2:14
In other words,
products that are not discontinued.
2:18
When I run this,
I get a complete list of products,
2:21
the categories they belong to,
as well as pricing data.
2:24
Nothing that special, but as I said,
a CTE is just a result set.
2:27
So I'll turn this into a CTE by
adding the WITH keyword, a name,
2:32
AS, and wrapping the query in parentheses.
2:38
And to format this little bit,
I'll indent inside the parentheses.
2:43
Now, when I run this, I get an error.
2:48
The code I just wrote only creates
the common table expression.
2:51
To build a complete query, we must
had a select statement to query it.
2:54
Like I said, the common table
expression acts just like a table.
2:58
So you can query against
the CTE name like this.
3:02
I'll select everything
from product details.
3:06
And then I'll order it by the category
name and the product name.
3:10
The name, product_details,
3:19
gives us a clear idea of what kind of
data we're including in this query.
3:21
And since the CTE acts just like a table,
we can query it just like a table.
3:25
For example, say I wanted to determine
how many different products and
3:31
the total stock count for each category.
3:35
I can just modify the SELECT
statement to get at that data
3:38
with the common table expression.
3:41
I'll select the category name but
this time, I'll do a count.
3:46
And this will be the unique product count.
3:49
I'll also sum or total up the number
of units we have in stock.
3:53
And I'll get that from
our CTE product details.
3:59
I'll group them by category name,
and then I'll order them by count.
4:03
This is really all there is to creating
and using common table expressions.
4:12
But you might be wondering, why use a CTE?
4:16
These examples aren't all that exciting.
4:19
And you can get the same results
with fewer lines of code.
4:21
That's true, and
you probably won't use a CTE for
4:24
a short query like the one I just showed.
4:27
However, CTEs provide several key
benefits for organizing long and
4:30
complex SQL queries.
4:34
First, your code is more
readable using CTEs.
4:36
As I mentioned earlier,
4:40
the named query gives you information
about the returned results.
4:42
Names like product details,
active customers, or
4:45
recent transactions are clear and
understandable when you see them.
4:48
Readable SQL is a big help for
4:53
other folks who have to work with
your queries and a big help for
4:55
you when you later have to return to
your SQL and remember how a query works.
4:59
Second, CTE's help you organize
queries into reusable modules.
5:04
Look at the code we've written,
I can easily just grab the CTE,
5:09
copy it and then paste it into
a new query, knowing that I've got
5:14
all the relevant SQL needed to
capture the product details I'm after.
5:19
Likewise, you can combine multiple
CTEs into a single query.
5:23
I'll show you how in the next video.
5:28
So you could piece together CTEs almost
like Lego blocks to create complex ways
5:30
to look at your database data.
5:35
Finally, organizing queries
into common table expressions
5:37
matches how we think through
data analysis problems.
5:40
We often think of our queries
as result sets to begin with.
5:44
For example, say you wanted to figure out
which product categories were most popular
5:47
with which customers in the past month.
5:52
You might start off with
something like okay,
5:55
first I need all the orders
in the past month.
5:57
Then I need all the active customers.
6:00
And finally, a list of all of our
products and their categories.
6:03
It's natural to think
of queries in that way.
6:08
And common table expressions help
you write queries that match your
6:10
thought process.
6:13
For example, each comment here would
represent one common table expression.
6:14
In the next video,
I'll show you a common use for
6:20
common table expressions, converting
subqueries to more readable code.
6:22
You can close the SQL playground here.
6:27
I'll have a new playground for
you in the next video.
6:29
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