9 hours 41 minutes
I welcome back to module six. This is less than 6.4 common table expressions or C t e for short.
In this lesson, we're going to review common table expressions. So let's go ahead and go over these bullet points.
They improve query organization and complex queries. So in complex queries, when you don't have access to a common table expression, let's say you're in my sequel five. For example. You are typically gonna handle these types of situations with a Dr table or sub quarry, and some other situations will be much more complicated to deal with
because of this.
So with the introduction of table expresses, expressions regain access to a temporary name result except all of the major table crowd functions. So when you generate a table expression, you can perform inserts, updates, deletes and selections against them,
which is very useful in some situations.
So to move onto the second bullet point, they're much more suitable in some situations when compared to derive tables. That's because of those crowd functions, and a lot of the time they're easier to read. I myself much prefer to use a common table expression than a bunch of Dr Tables by a sub choirs. Now, with that said, let's move into the development environment.
Take a look at some of these
Hi, Welcome back to the development environment. Let's go ahead and get started with coating some of these table expressions and see what they are. Review what they look like.
So we're going to start with the table expression code words with
I'm gonna say C t e
Let's make sure of that syntax, right? So just, say, select
And then down here we will perform a select star from C T.
That's good and run that
and that's working. So I got the Syntex right there. That's good.
I think it's a little different. And Microsoft sequel.
Or maybe it was DB two. Anyways, this is how you do it in my sequel.
So what do we want to dio? Let's let's implement a couple of these derived tables that we use in the previous lesson as common table expressions and see what they look like. So let's get the teachers
select Star from
user, of course, is you see
join you user types you t
ut that I d equals u c dot user type i d.
Let's change the name of the CTE to teachers
or teacher courses.
we need that filter in there. So where
UT that title equals teacher
Onda se you see dot
school I d
you see that?
Of course I d And you see that,
uh, user I d as a teacher, I d
It's going and run that, but we're going to need to change this selection from
All right. So we have a table expression.
So we have defined what? This table is up here, and then we're selecting from it down here. Now, if we weren't going to do it this way, we'd use a derived expression like we did in the previous lessons, and that would look like,
um, like this,
which is gonna little very familiar.
This should still work.
Now, the reason I prefer the table expression is because I like to keep
these parts separated.
So I know I've selected from a table object here, and if I'm interested in what this is, I can just look at my table expressions say OK, that's what teacher courses is.
Where is when you have a bunch of derived queries embedded in a complex
query like we did in the previous lesson. It could get very confusing toe look at aunt to read, and table expressions make it easier to read. Now table suppressions can get out of control, and you can start to chain a bunch of those together and make them confusing as well. But in general, they remain easier to read when compared to the derived table equivalent.
Okay, so let's add another table expression. So we need to do is Atacama and say,
Let's say student course, right student
and we're going to select Star. Actually, we're gonna just copy this from above.
We're gonna change the title to students.
Andi, we're going to change this to
student I d. And we're gonna fix the spelling on this one.
It's gonna run that
now. We don't have to use the table expression. So if I run this, it still runs. And I have done nothing with this table expression.
But let's say I want to connect it to Well, the night will just join them like regular tables, so join
join student courses. Unfortunately, the intelligence on
every idea I've ever tried gets lost. When you start working with table expressions or derived tables
on sc that school I d
t see that school I D
seed? Of course I d
t see that course I d
Let's go ahead and make sure that's working now. If I try to run that by itself, it's not gonna work again and air.
And that's because this whole script tests to run together. I run this by itself. This part has not been run, so it has the program or the sequel. Engine has no idea what teacher courses or student courses is. So control a and run them all together.
So here we have a course 20 with Teacher
and soon idea four to. So again, we know that teacher 51 taught student 42
in course 20.
So let's clean this up a little bit.
Duty CDA School I d.
You see that teacher I d?
as he that
Make sure this runs
and let's put the course in there, too.
It's a TC that course I d.
Let's give that a run
So we got a course 20. Teacher, 51 taught. Teach student 42. All right,
that's all great. But what if we want to make this a table expression? Well, that wouldn't be hard.
So we can say comma.
Well, say, um,
so you can chain these together
and use the previous table expressions.
So now, at the bottom. If I just do a select star
from teacher student courses
and I, of course, spell it right
with the exact same results. And you think this is a great example of table expressions? Were we've created a teacher course table expression, a student course
table, expression. Then we connected those two into an additional third table expression and then at the very bottom, were selecting from that final table expression. Although we don't have to use the last table expression we could select from the 1st 1 the 2nd 1 or the 3rd 1 or either one that we want to.
So that completes this section on table expressions. So let's go ahead and head back over to the summary section,
and that brings us to the 64 summary. So what do we do in this lesson, we reviewed table expressions. We took a look at their definition and their implementation. We change three table expressions together in the development environment and is a reminder CTS Permit the following operations select, insert, update or delete.
So if you change a bunch of them together, you can select from the 1st 2nd 3rd or the last one. How many ever that you have. And they help with segregating your area of concerns in your primary query.
So that completes this lesson, and I hope to see in the next thank you.