9 hours 41 minutes
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.
So let's go ahead and get started.
Let's switch this left, joined to a right joint
and see what kind of data we get back now with the right join, we expect it to return
records that are existing the right table
and return null if they don't exist in the left table. So let's run this.
So we're getting
some Knowles. So what would this be? This looks like it's gonna be employees
that are not managers, so they're not ever mentioned or they air never mentioned in the reports to field. So
let's see. Leslie Jennings, with employee number 11 65 is never mentioned in the reports to
si reports to no mention of 11 65 so that checks out.
But this query is a little bit messy. It's got a bunch of data, and by itself, it doesn't really,
you know, intuitively say what? It's returning back. So let's go ahead and do this. Let's go
e p dot employee number
So what do we have here?
We have employees that are not managers. So if we needed a list of employees that were not managers,
we have it. And we did that by switching the left. Join to a right joint.
Now let's see.
What if we do is not know.
What list do we get back then?
Well, we get a rep replicating results
for the managers,
and it's very similar to the left joint variant. Let's go ahead and get rid of
these columns right here.
So we got replicating results sets. Let's do a group by statement. Now just Teoh
go over. What a group I does is
on the columns that we list in the group by statement. If they duplicate, it's going to merge them into one result.
So when we grew by employee number and name,
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
this except instead of two. It's four.
So let's go ahead. And you're that group
man dot employee number.
And we need to include the first name and last name
everything that we're using,
or we would have to aggregate that. So we would have to tell it what we want to do.
Um, when there's multiple records back.
So if we run this
and here is a list of our managers,
And if we want a list of employees that are not managers, we just get rid of this. Not.
And there are the employees that are not managers, cause they're never mentioned in the reports two column.
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.
And here we are at the right joins summary. So let's quickly review
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,
make it the right table and then use a right
join, you'll get the same results as using it as left table and using a left join.
And the blow Venn diagram is shown that be returns all records with data shared in a as well
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.
The Microsoft Azure Test is a premium Cybrary assessment created by Interview Mocha. The exam ...
AWS Data Engineering
The AWS Data Engineering test is a premium Cybrary assessment test created by Interview Mocha. ...