Video summary
The video introduces Common Table Expressions, or CTEs, as a powerful feature in PostgreSQL that allows users to create temporary named result sets specifically for use within a single query. Unlike traditional temporary tables, a CTE does not persist beyond the execution of the specific statement it is defined in; it exists only momentarily during the query process. The tutorial begins by explaining the basic syntax, which involves using the keyword "with" followed by a name and an "as" clause to wrap a subquery in parentheses. This structure effectively acts like a named subquery, enabling developers to treat complex intermediate results as if they were standard tables that can be filtered, joined, or manipulated just like any other dataset within the same session.
A primary use case highlighted is the combination of CTEs with window functions to solve complex ranking problems efficiently. The presenter demonstrates a scenario where one needs to identify the top-ranked individual within each species based on estimated net worth. While this can be achieved using a window function like "rank," attempting to filter for the top result directly in the main query often leads to errors because the alias created by the window function is not accessible outside its immediate scope. By wrapping the ranking logic inside a CTE, the resulting ranked data becomes available as a table-like entity, allowing the user to easily apply a "where" clause to extract only those records where the rank equals one, thus isolating the highest value in each partition.
The lesson further explores the flexibility of chaining multiple CTEs together by separating them with commas, which allows for building increasingly complex queries step-by-step. This approach is particularly advantageous when dealing with nested logic or when standard filtering conditions cannot be applied directly to the underlying data due to how it is processed. The video emphasizes that while subqueries can technically achieve similar results, CTEs generally offer superior performance and readability, especially in scenarios involving deep nesting or multiple window functions. Ultimately, the presenter concludes that CTEs are an essential tool for organizing complex SQL logic, making queries easier to read, maintain, and execute without the overhead of creating actual temporary tables.
Read the full video transcript
What's going on everybody? Welcome back
to another video. Today we are
continuing our Postgra SQL series and in
this lesson we are looking at CTE. Now
CTE stands for common table expression.
It allows you to create a temporary
named result set that only exists during
that query and within the query. It
isn't like a temporary table where you
can reuse that temporary table later on.
It only works within the query. And so
we're going to take a look at the syntax
of it and an actual use case of why you
might need to use a CTE. Let's look at a
super simple example of a CT first and
then we'll look at a use case. Now the
syntax goes like this. It starts with a
width and then we need to name it. So
we're just going to say a basic
CTE. Then we say as and we wrap all that
we're going to kind of put in this CTE
inside of these parenthesis. Now, we're
going to use this table right here.
We're going to say select and we'll just
choose a couple columns here. We'll do
the character
name. We'll do the species and then
we'll do the has both arms. Now, all we
have to do is specify from this table
and we'll place it right here. Now, if
we just ran this, we have this kind of
subset of data. You can almost look at
this as like a subquery. We have a query
within a query. There's quite a bit of
nuance and difference between the two,
but you can think of it like this. So,
we have this subset of data that we're
placing within this CTE right here. And
now we can query off of it by saying
select everything from this CTE that we
created. So, let's go ahead and run
this. And now you can see we've selected
everything from within the CTE. Now, we
can filter off this. So we can write
anything we want to like a basic query.
So we can say where and let's take has
both arms is equal to yes. So now we can
run this and there we go. So we can
filter and do anything that we would
want to do like a regular query. Now
let's look at a real use case of this.
Let's go back and let's come right down
here. Let's just select everything. Now,
in the past several lessons, we've been
looking at window functions. And window
functions are super super useful. And
let's actually write one out and then
we'll see why we would need a CTE in
order to do what we're about to do. So,
let's select the character_ame.
And then we're just going to take the
estimated net worth and then we'll do a
rank and we're going to do that over and
we'll do a partition by on the species.
And I'll actually put the species up
here as well while we're here. And then
we're going to order it. We're going to
say order by the estimated net worth
descending. So let's go ahead and run
this. And so right here we have
specifically in the humans. This is the
one we're going to look at the most. We
have humans. We have 1 2 3 4. Then it
jumps down to six. This is really useful
data. And within each species, we have a
rank one, of course, right? And I want
to only pull out those rank ones. I just
want to know who is the person within
each species that has the highest
estimated net worth. That's all we're
trying to do. We're trying to figure out
number one. Now, what you might want to
do is just say where, and we could kind
of name this. We could say as uh
ranking,
and we could say where the ranking is
equal to one. And if we try to run this,
we're going to get an error. It doesn't
exist. It just exists in that window
function. But what we can do, and let's
get rid of this.
What we can do is we can place this
within a CTE. So we're going to say with
we're going to say ranking
net worth and we'll say as we're just
going to wrap all of this within the
CTE. So now this is the ranking net
worth. So let's select everything from
this ranking net worth. Let's go ahead
and run this. And now we can filter on
this just like a regular table. So we
can say where the ranking is equal to
one. And let's go ahead and run this.
And now we very quickly filter down our
data to only the highest estimated net
worth people within each of the species.
Now, we can filter this down more very
easily, but I'm going to show you that
you can actually chain these CTE and
have multiple CTEs in one because right
here, we can come in and we can add in
another CTE.
All we have to do is put a comma right
after this. And I'm going to kind of
format a little different. So, I'm going
to put a comma here. And then we're
going to say ranked one as. And then
we're wrapping this in a parenthesis.
And then we can query off of this table
right here. So now we can query off of
this ranked one. So we can say select
everything from ranked one. And let's
run this. I know it's getting long. I
can actually uh bring this down a little
bit so we can see everything.
So now we can filter on this again. I
can say where the species is equal to
human. And let's go ahead and run this.
And so you see we have our first CTE
right here. And then we put a comma and
we use this table or this result set to
use right here where we then filter and
use that and create a new CTE and then
we can select it. And guess what? You
can just keep doing this as long as you
need. Now the reason for doing this over
something like a subquery is mostly
performance because subqueries typically
don't perform very well. they can be
pretty heavy queries which just means it
takes a lot of compute in order to
actually process them. Whereas CTE are
really good at performance and so if you
have a query within a query within a
query and you need to kind of keep going
down but you have window functions and
it's getting complex and you need to
kind of organize it CTE are incredible.
I use these all the time. They're just
really, really useful. And there are so
many use cases for this. When you query
your data and you're like, wait, I need
to query this again. But because of how
the data is being processed, you can't
then just create a wear statement or a
having statement. You then need to
create a CTE and then query off of that
result set. Now, you'll notice this is
only going to live within this query. If
I come right down here, because we were
doing this, I can come down here. I'm
going to come outside of this right here
and just run this by itself.
We're going to get an error because this
ranked one only exists in this query. It
does not exist outside of it. It doesn't
save it like it would a temp table or
something like that. It is purely
existing only in this query. So, I hope
that that was helpful in understanding
CTE a lot better. If you learned
anything and you like this video, be
sure to like and subscribe [music] and I
will see you in the next lesson.