Explicit vs. Implicit Joins

00:00

I welcome back to module to this is a lesson to point to explicit verse. Implicit joints. If you took the introduction to Sequel course here on separate that I t. And that course we used implicit joints

00:13

in this course, we're going to briefly discuss explicit verse implicit. And then, from here on out, we're going to use explicit joints. Now you should be able to recognize both types because it is common to find both types out in the field.

00:27

So in the past, the explicit enter join did not exist in this era. All of all of your joints would have been written with the implicit syntax. With the introduction of explicit joints, it is recommended that you promote explicit joins over implicit joints.

00:43

And the reason you do that, isn't it. It helps keep code clean, and it does this by clearly separating your joints from your filters.

00:52

Now, as I mentioned before, you should be comfortable with both, as both are commonly used in the field. Still

00:59

and again in this course, we briefly discussed what they both are, and then we use explicit for the remainder of the course. Now, with that said, let's jump over to the virtual machine and take a look at exactly what we're talking about.

01:14

I welcome to my virtual machine have D Beaver Open, and I'm taking a look at the Classic Models database, which is one of those tutorial databases available online. I will provide the script as a resource so that you can download it and install it if you would like to follow along.

01:30

So if we go over into this tab where I have written some sequel code, we'll take a look at the implicit versus the explicit joins with some simple queries. Now, within the classic Models database, we are joining the employees table and the offices table I've given the employees and

01:49

an alias of E. P. And I've given office is an alias of O F. F.

01:53

So this right here

01:56

is the implicit join.

01:57

We haven't used the joint keyword like we have down here. We've just implied that there isn't a joint.

02:05

Hence the implicit keyword with our wear statement. We have said where office code from employees equals office code from offices,

02:14

and I've also required that the office city

02:16

equal Sanford Siskel. So if I highlight this and I run it. Of course, either that is the results down here.

02:24

And if we take a look at this, where we at this query right here, where we use the explicit joint, we can see that we're not using a where to join the tables were using a joint key work

02:38

to join the tables. And if we run, this will get the exact same results.

02:45

And this is kind of a trick question here or a trick observation. Is there a difference between these two?

02:53

No, there's not. This is even more explicit.

02:57

So both these are explicit

02:59

or considered explicit joins, but this one's even more because I'm indicating that this is an inter joint.

03:05

But if you do not use the inner keyword than it's assumed or implied to be an inner joint

03:10

now, how does this help keep the code clean? While if we did this right here,

03:16

we could actually

03:20

do the joining of the tables after the filtering of

03:24

the office.

03:27

So if we run, this will get the same results,

03:31

and now we have a filter, then adjoin.

03:35

Now, this is where things could potentially get confusing. If the programmers not being careful now, you really want to have a good reason to do this. But nothing would stop you if you were writing a complicated joint and you wanted to do

03:46

table joint filter table joint filter table joint filter table joint,

03:53

you could do that, and you could get the same results as if you properly wrote to join where you separated your filters and your joints. And that's part of the reason we use these explicit joints. So if I take this

04:06

actually, if we run this, we'll see that we get the exact same results.

04:13

But if I tried to do the same thing with this,

04:15

I'm actually going to get an air,

04:16

so I can't have aware before my joints and I can't use the filtering before my joints.

04:23

I try to run that I have an heir,

04:26

so I have to keep my joints separated or I have to do my joints first, then my filtering.

04:31

I can't mix them so there's less freedom in the explicit syntax,

04:39

but it's still within the confines and allows you to write efficient coats all the same. There's not really a good reason to mangle your joints and your

04:49

filters together, but

04:51

I have seen people do it, and it was probably just code being tacked on. So it's nice to use explicit and know where your joints are and know where your filters are, especially in complicated queries.

05:04

So that completes this portion of the lesson. We're gonna go over to the summary and just briefly discuss what we went over in this video,

05:14

and that brings us to the explicit verse Implicit summary. So what did we discuss in this lesson? While we discussed what the explicit join waas we discussed that we want to use explicit, over implicit in new queries.

05:28

However, we also discussed that it's important to be able to understand and recognize both styles as you are likely the sea, both styles in the field. And again, the reason we want to use explicit is because it promotes stronger code organization.