9 hours 41 minutes
I welcome back to module to quick review. This is less than 2.3 the inner join,
and in this lesson, we're gonna go over the basics of dinner join. But to keep things a little interesting, we're going to use a self reference on one table to create an inner join.
And the table I'm interested in is this employees table, and the joint that I'm interested in involves this constraint. So if I click that, I'll see that it highlights the reports to field and the employee number field.
So without. I've seen this type relationship before, and I suspect that it's an employee to manager relationship, and this constraint requires that the reports to field be a really employee number.
So we can't, you know, employee number negative one probably doesn't exit exist, so we won't find that number in this. And these kind of constraints help protect database integrity because it's easy for a programmer or a front end user to make a mistake, type the wrong number and put in a reference to something that doesn't exist.
Constraints again. Help us avoid that.
But we're more focused on the inner join in this lesson So let's go ahead and get started.
So if we go to this script and take a look at a simple query I wrote,
we can see that I've joined
or that I'm selecting from the employees table. I've a liest it with an E. P for employees.
And then I'm joining
another reference to employees table. But I've alias this reference with man for manager,
and I'm joining E. P reports to
on man dot employee number. We could see that generates the results down here,
and I can run this again just to get the exact same results.
We can see that you know, Diane Murphy looks like she supervises two people Mary Patterson, three people.
And then there's even more down below that
now. The key take away about the inner join is that it requires a valid reference in the joining columns to return data.
if, for example, this man dot employee number doesn't exist or set to know, we won't get a record back or any data back on that joint at all, we won't get any P back, even if any P employee exists
and the same is true for the reverse two if something exists on the man, but not on the E. P record again, we're not going to get anything back. And that is how the inner join works requires a reference on the joining columns on both tables.
With that said,
let's go ahead and let's go ahead and head over to the summary
and that brings us to the inner joins summary and just ah, review. The joining key requires representation on both tables. We can see that when we look at the Venn diagram for this as well, which comes from Wikipedia, Table A and table B. Both share the results set in the middle with shaded orange.
And that is the big take away about the inner joint Is that when you join on those columns, both tables have tohave
a result to return any data at all back, which is different from the left and right. Join which we will be going over in the next lessons. Hope to see you there. Thank you