7 hours 36 minutes
I welcome back to model to databases. This is less than four sequel joins for currently in sub lesson 4.4, the Left Outer Joint and the previous lesson. We discussed the inner join, which was a relationship that required representation in both tables to return within the data set.
We're going to deviate from that idea a little bit and return data that exists in one table
that's also has dead data and table, too.
So let's look at the Venn diagram for that. And again, this is from the Wikipedia page for sequel joints. We can see that a left outer join of Be on a will return all results that are in Table A along with results that also exists in table Be. Now, what does that look like in a, uh,
a sequel query? Because without knowing too much about it, you might be confused. And you might think, Well, how do I do that? How would I return all the data in a with only some of the data and be And the way that plays out in sequel is that
tape results that have results in a but not be will return a knoll for the B data sets. So let me show you what that looks like. So let's get to writing this quarry and again we're going to focus on the employees and the department manager table.
So we're going to select. I don't know what I want yet, So I'm gonna drop down to the from statement.
From what tables? I need the employees EMP table or I mean, I'm going Thio given Alias event
and the left outer joints in tax is performed like this so you don't put a comma
after the first table,
you just drop down and you type out the words left outer.
uh, the other table, which is going to be the department manager,
and I'm gonna give that an alias of demon again
on EMP dots.
is equal to demon dot empty number again,
and that's our left out or join right there
and we're gonna go ahead and select the
Empta M number.
The first name pretty much the same
results said that we selected last time
Ah, department number
comma. Don't forget your commas.
The demon dot
from dates comma and the demon dots. Two days
and we're done. Let's go ahead and run this,
okay? We got a bunch of results back,
and there are a ton of employees in this and it will take too long to really
cycle through and see if it makes sense what we're getting back. So let's go ahead and just go back to the top
and sort this data by department number.
Now, Before we do that,
we can see how this is being represented. So we get everything back that's in table A but in the department table, where we would get data back, it's no and no is an idea that means nothing. It means nothing has entered here. Nothing exists here,
which is what we do expect, because
this is no over here. Nothing. But let's go ahead and sort this and make sure we did get something back because we should have some results returned back. In fact, we should get the same number of records that we had in our inner join as a populated data in these fields.
I'm going ahead and click. Yes, it's just telling me or warning me that there's a lot of data for it to sort through and that's fine.
I'll give it one more click,
and there we go.
So this looks like it's the same results that that we had when we were working with the inner join. And we can see that four records that have representation in both tables both are filled out. However, in the employees table where they weren't in the department manager table, we still got their records back.
However, in the department fields, we got no back, which is nothing. And that, my friends,
is the left out of joint.
So I hope you got your left outer. Join working
and I will see you in the next lesson. Thank you.
Penetration Testing and Ethical Hacking
The Penetration Testing and Ethical Hacking course prepares students for certifications, like CEH. This course ...
7 CEU/CPE Hours Available
Certificate of Completion Offered
This free course introduces the student to intermediate concepts found in the implementation and application ...
10 CEU/CPE Hours Available
Certificate of Completion Offered