Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
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.
00:17
We're going to deviate from that idea a little bit and return data that exists in one table
00:23
that's also has dead data and table, too.
00:26
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,
00:44
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
01:00
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.
01:19
So we're going to select. I don't know what I want yet, So I'm gonna drop down to the from statement.
01:25
From what tables? I need the employees EMP table or I mean, I'm going Thio given Alias event
01:34
and the left outer joints in tax is performed like this so you don't put a comma
01:41
after the first table,
01:42
you just drop down and you type out the words left outer.
01:49
Join,
01:53
uh, the other table, which is going to be the department manager,
02:00
and I'm gonna give that an alias of demon again
02:05
on EMP dots.
02:07
EMP number
02:09
is equal to demon dot empty number again,
02:15
and that's our left out or join right there
02:19
and we're gonna go ahead and select the
02:22
Empta M number.
02:23
The first name pretty much the same
02:29
results said that we selected last time
02:32
demon dot
02:35
Ah, department number
02:38
comma. Don't forget your commas.
02:42
The demon dot
02:44
from dates comma and the demon dots. Two days
02:50
and we're done. Let's go ahead and run this,
02:53
okay? We got a bunch of results back,
02:57
and there are a ton of employees in this and it will take too long to really
03:01
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
03:07
and sort this data by department number.
03:09
Now, Before we do that,
03:12
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,
03:30
which is what we do expect, because
03:32
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.
03:51
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.
03:59
I'll give it one more click,
04:02
and there we go.
04:04
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.
04:21
However, in the department fields, we got no back, which is nothing. And that, my friends,
04:28
is the left out of joint.
04:30
So I hope you got your left outer. Join working
04:32
and 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