Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

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
04:35
data not in a will return, Knowles. So that completes the right join lesson. And I hope to see in the next lesson. Thank you.

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor