3.12 Table Expressions

00:00

Hi. Welcome to Module three. Sequel programming were at Lesson four. Table expressions were in sub lesson 4.2. Introduction to table expressions on We're going to hit the ground running with this. This first lesson is gonna be a pretty short sweet to the point. We're going to take a look at a simple table expression,

00:19

and the first question you have is probably, well, what is a table expression?

00:24

Well, the an easy way to look at table expressions is that they are temporary tables or temporary reviews that exists for the duration of your quarry Run. So you create them to use an inquiry that you're writing. And then when you execute that query, they're there

00:42

for that brief period, period of time

00:45

in system memory that your query is actually running. So it's like a temporary view. So it might be a data set

00:51

that is close to being a view, but you don't really want to make it of you. You just have to use it in this query. It's gonna make your query easier to work with, so you're just using it in your current query, and you don't really intend or plan to use it elsewhere.

01:06

So let's go ahead and get started. I think if I show you what I'm talking about, it will be very easy to understand. So we're going to use a table expression in our career e to create a temporary table that is just the manager's off the company.

01:22

So let's go ahead and get started. We're only going to need one table

01:26

to create this table expression and the table expression will result in a table that contains Onley our managers.

01:33

So let's go ahead and get started.

01:34

So to start with the table expression, you start with the

01:38

with keyword

01:41

with the table expressions name. So we're gonna call it managers

01:45

and then as we see that key word a lot and we're gonna put the brackets in there

01:53

and here's where we're going to create our temporary table or our table expression. So we're gonna select.

02:00

I don't know what I want yet, so I'm gonna drop drop down to the from statement. So from

02:06

E p employees,

02:12

where

02:15

supervisor I d is not? No,

02:20

because we don't want to see the results of, um,

02:24

employees that do not have a supervisor and we know the president of this company doesn't have one, so that would return a No. So we don't want that.

02:34

Um, let's see, What do we want? We want the company I d.

02:39

And we want the supervisor, I d.

02:44

And right now we would get a lot of duplicates because a supervisor could have many subordinates. So for all those subordinates, we would see his record or his super supervisor I d replicated. We don't want to see that. So we're going to use a statement you were exposed to in a previous lesson. The group by statement.

03:02

Now what is the group by statement do again?

03:05

Well, when it gets to a result set where the group by columns are repeating, it's going to say, OK, these values are all the same. I'm gonna compress them into one rope

03:17

and get rid of that duplication, and that's exactly what we want. So we're gonna group by company I D supervisor I D, which will give us a unique data set now, another way that you could do the same thing, but it is slightly less efficient

03:36

is to just use the distinct keyword,

03:38

which basically says the same thing and says,

03:42

Hey, for these records, anything that is not unique, go ahead and discarded. I want only unique records. I don't want duplicated records, but

03:51

we're going to use the group by statement because it's good to get used to using that as it has many more uses,

03:58

then just making a distinct results set.

04:01

So group by

04:05

company I d. Supervisor idea. So this is our temporary table.

04:12

So how do we use this? What? We're simple. We're going to select

04:15

star from managers.

04:19

Okay, So managers

04:23

is a table or a temporary table or a table expression created by this query and given this name. So it's similar to a view that you create on the fly and then select from

04:34

So let's go out and run this and see what we get.

04:38

This is exactly what I expected to get. So we have company, too, and we have all the managers. So we have created a table expression that generates

04:48

a manager's temporary table

04:51

from the following quarry, and then we're able to select from it.

04:56

Now you might be asking yourself Well, okay, great. We have this temporary table and were selected from an awesome. Can we do everything that we can do with a regular table with it? Can we add other tables and join to it? And yes, you can. You can use it just like you would a regular table.

05:13

So what this allows you to do is again abstract some complexity out of your primary problem.

05:19

So

05:20

if I was facing a problem where I just needed the managers But I didn't wanna have to complicate complicate my query

05:29

with unnecessary bloat

05:30

to select the managers, I could use a table expression, name it the managers, and then use that as a table