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
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