SQL - inner join before left join

Question

I have come across some sql that has an inner join right before a left join.

From Table t
     INNER JOIN vw_User i ON t.UserID = i.UserID
     LEFT JOIN UserTypeTwo it ON t.UserTypeTwoID = it.UserTypeTwoID

Can someone please help me visualize how this works. I can visualize the two joins separately, but since they are one after the other, how do they work together?

Here is some info http://stackoverflow.com/questions/9614922/does-the-join-order-matters-in-sql — TGH, May 29 '14 at 20:25

score 0 · Answer 1 · edited Jun 10 '14 at 12:26

0

What will happens is that you are just appending more columns to Table T. Basically what happens is that Table T has no relationship to UserTypeTwo, but it does to vw_user. Then you need some data from UserTypeTwo but since Table T can't join to userTypeTwo you need a table to connect them both, which is where vw_user comes in which should have a relation to UserTypeTwo.

Basically this query is being used because you need columns from Table T and UserTypeTwo(and probably vw_User as well) so you need to join all three of them.

Table T has FK for vw_User
vw_User has FK to UserTypeTwo
UserTypeTwo has column that you need

(it doesnt necessarily need to be a foreign key but I'm assuming you know this since you have join knowledge).

I hope I was able to clarify what is happening.

edited Jun 10 '14 at 12:26

SOfanatic

5,523
5
36
57

answered May 29 '14 at 20:30

ZeRaTuL_jF

582
2
4
20

Table t is related to UserTypeTwo by the UserTypeTwoID – JAck28 May 29 '14 at 20:35
Opps sorry scanned through that very quickly, but regardless all you are doing is appending more columns to table t. Its very common that you need different columns from different tables that the Main table has relations to, so basically it works by first it inner joins T with vw_User, and now it has all of those columns and then it joins that result to UserTypeTwo. – ZeRaTuL_jF May 29 '14 at 20:43

score 0 · Answer 2 · answered May 29 '14 at 20:32

0

IMO, no better way to express joins than like this:

enter image description here

answered May 29 '14 at 20:32

Roberto Navarro

948
4
16

I refer to this visual all the time. I just don't understand how combing two joins one after the other affects the visual. – JAck28 May 29 '14 at 20:34

score 0 · Accepted Answer · answered May 29 '14 at 20:37

0

See the following image, don't have enough rep to embed it. https://i.stack.imgur.com/1S8fF.png

answered May 29 '14 at 20:37

Will

104
3

SQL - inner join before left join

3 Answers3