Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Copakid
Starting Member
2 Posts |
 Posted - 2013-08-22 : 21:04:51
|
| I am trying to get my head around some joins that have previously been written. I understand what the joins do, I am trying to get a visual, not only for my self, but othersThe code is From DT_1 Cross Join CDyInner JoinB2COn DT_1.Acct_key = B2C.Acct_KeyInner JoinALCOn DT_1.Acct_Key = ALC.Acct_KeyInner JoinALOn DT_1.Acct_Key = AL.Acct_KeyLeft Outer Joinsm1On DT_1.Acct_Key = sm1.Acct_KeyIf anyone is able to draw quickly what this would look like it will be greatly appreciatedThanksCopakid |
|
|
erikhaselhofer
Starting Member
30 Posts |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|
Copakid
Starting Member
2 Posts |
Posted - 2013-08-25 : 17:23:50
|
| I am well aware of the what the joins do and how they look. I think the question is the order they are processed. I came up with this. Is it correct?The tables have the following data countsDT1 4 recordsCDY 4 recordsB2C 4 records with 3 of them also in DT1ACL – 4 records with 3 in DT1AL – 10 records with 3 in DT1Sm1 – 20 records with 1 in DT1The first join is a “DT_1 Cross Join CDy. This will produce 16 (4 times 4) records. We will call the results A.The second join is the Result of the first join – A, inner join B2C. This will return 3 records. We will call the results B. The third join is the Result of the second join – B, inner join ALC. This will return 3 records. We will call the results C. The fourth join is the Result of the third join – C, inner join AL. This will return 3 records. We will call the results D. The last join is the result of the fourth join – D, left outer join SM1. This will return 3 records Copakid |
 |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-08-28 : 03:39:03
|
| i think u need to read thishttp://www.sommarskog.se/query-plan-mysteries.html |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-08-28 : 12:10:19
|
quote:
The first join is a “DT_1 Cross Join CDy. This will produce 16 (4 times 4) records. We will call the results A.The second join is the Result of the first join – A, inner join B2C. This will return 3 records. We will call the results B.
This is already off as DT1 contains 3 of the 4 values that B2C contains. So, the INNER JOIN to BC2 would produce 12 rows since the result of the cross join would have 12 matching rows.If you create sample tables with data you can run each join and see the output, which might help you visualize it better. |
|
|
|
|
|
|
|
|
Diagram of Joins