Right Join

00:00

I welcome back to module to quick review. This is less than 2.5, right join in the previous lesson. We went over the left joint, and in this lesson, we of course, are going to go over the right joint. And we're going to continue working with the query that we've been working with for both Ienner join and the left joint.

00:18

So let's go ahead and get started.

00:20

Let's switch this left, joined to a right joint

00:24

and see what kind of data we get back now with the right join, we expect it to return

00:32

records that are existing the right table

00:35

and return null if they don't exist in the left table. So let's run this.

00:41

So we're getting

00:43

some Knowles. So what would this be? This looks like it's gonna be employees

00:47

that are not managers, so they're not ever mentioned or they air never mentioned in the reports to field. So

00:56

let's see. Leslie Jennings, with employee number 11 65 is never mentioned in the reports to

01:03

si reports to no mention of 11 65 so that checks out.

01:08

But this query is a little bit messy. It's got a bunch of data, and by itself, it doesn't really,

01:15

you know, intuitively say what? It's returning back. So let's go ahead and do this. Let's go

01:22

where

01:25

e p dot employee number

01:29

is No.

01:32

Okay,

01:34

So what do we have here?

01:36

We have employees that are not managers. So if we needed a list of employees that were not managers,

01:44

we have it. And we did that by switching the left. Join to a right joint.

01:49

Now let's see.

01:52

What if we do is not know.

01:56

What list do we get back then?

01:59

Well, we get a rep replicating results

02:02

for the managers,

02:05

and it's very similar to the left joint variant. Let's go ahead and get rid of

02:10

these columns right here.

02:14

Rerun this.

02:15

So we got replicating results sets. Let's do a group by statement. Now just Teoh

02:23

go over. What a group I does is

02:25

on the columns that we list in the group by statement. If they duplicate, it's going to merge them into one result.

02:34

So when we grew by employee number and name,

02:38

it's gonna it's gonna take a look at these two first results Sets is going to say Wait a second employee number 1002. That's the same name. Diane Murphy is the same. I'm gonna return this as one result instead of two, and it's going to do the exact same thing for

02:53

this except instead of two. It's four.

02:57

So let's go ahead. And you're that group

03:00

by

03:05

man dot employee number.

03:09

And we need to include the first name and last name

03:15

everything that we're using,

03:17

or we would have to aggregate that. So we would have to tell it what we want to do.

03:22

Um, when there's multiple records back.

03:27

So if we run this

03:30

and here is a list of our managers,

03:32

okay?

03:34

And if we want a list of employees that are not managers, we just get rid of this. Not.

03:39

And there are the employees that are not managers, cause they're never mentioned in the reports two column.

03:46

So that completes this portion of the right joint. Let's go head over to the summary and quickly go over how the right joint behaves.

03:57

And here we are at the right joins summary. So let's quickly review

04:01

the joining key requires representation on the right table. It's just like they left joined but reversed. In fact, you could write a left or right join data that returns the same data set if you reverse both the joints and the joining command of right and left. So if you take the left table,

04:21

make it the right table and then use a right

04:24

join, you'll get the same results as using it as left table and using a left join.

04:30

And the blow Venn diagram is shown that be returns all records with data shared in a as well