Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
Okay. Welcome back to Montel. To databases. This is lesson for sequel joints and currently were in sub lesson 4.5, the right outer joint, which is the inverse of the left outer joint.
00:13
We're going to return all the data they exist and be with the related data exists in a
00:20
and the vin diagram for that looks like this. We get everything back from table B and we get to related data back from table, eh?
00:28
Let's go ahead and get started with that, then. And we're actually let's start where we ended it in. The previous lessons kind of demonstrate something about the right and left outer join their actually interchangeable. If you reverse the way they're reading their written, you can use a left outer and a right out of joint
00:46
to reach the same results set.
00:48
It kind of becomes a perspective of the programmer involved in writing the query. So let's take a look at what this looks like.
00:56
So here we have the previous query we wrote in the previous lesson.
01:02
Let's go ahead and just switch this to ah, right, outer join.
01:07
So right now we are right. Our joining on the department manager table. So B is the department manager table and a is the employees table. So our results set should shrink down toe on Lee the things that exist in V, and we'll also pull in the related data from the employee table.
01:26
Now the related data is defined is the MP alias?
01:30
So, for example, Empta employee number will come in from
01:36
are the employees table.
01:38
So let's go ahead and run this
01:41
and we see that our data set has indeed shrink, and it's fairly similar to the inner join that we used in a previous lesson.
01:49
However, if there were entries and the department table that did not exist in the employees table, we would get department injuries with no foot employee number. But that didn't occur in this test data, which is pretty much expected. So let's see if we can change this to be the same results set
02:08
that we used in the previous lesson,
02:12
and to exchange to make that change is actually pretty easy. So what I'm going to do is I'm just going to copy
02:19
to this or better state. I'm going to cut it.
02:23
I'm going to paste it in the above statement and the from table, I'm gonna cut out the other table. So I've replaced the previous table with the department manager table.
02:35
I'm gonna pace that in place of the demon alias table that I was using previously. I will make sure the spacing is correct.
02:44
Now, if I run this
02:46
a lot of noes back, let's go ahead and sort by the department number
02:53
sword again.
02:55
And sure enough, we ended up with same data set that we used when we had a left out of joint. What we did is we swapped the tables and then called it right out or join and got the same answer.
03:07
So the thing to keep in mind here is that you could rotate this where B is on the left and a is on the right and then switch the joint type and get the exact same data type back.
03:21
So if you happen to hear someone say, Well, you have to use a left outer join. That's not necessarily true. And if you change the perspective, you can use the right out of joint to get to the same data set. So that's something to keep in mind when working with left outer joints and right outer joins. Because sometimes you might be thinking yourself.
03:39
Which one should I use? And the answer is, Well, you could use either one. It's kind of
03:45
what perspective are you looking at the tables from? Well, that completes this lesson. I hope you got your right outer Join working. And I hope you learn some things about the left and the right outer joins and how they can sometimes be interchanged.
03:58
I will see you in the next lesson. Thank you.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor