SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Diagram of Joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Copakid
Starting Member

2 Posts

Posted - 08/22/2013 :  21:04:51  Show Profile  Reply with Quote
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 others

The code is
From
DT_1

Cross Join
CDy

Inner Join
B2C
On DT_1.Acct_key = B2C.Acct_Key

Inner Join
ALC
On DT_1.Acct_Key = ALC.Acct_Key

Inner Join
AL
On DT_1.Acct_Key = AL.Acct_Key

Left Outer Join
sm1
On DT_1.Acct_Key = sm1.Acct_Key


If anyone is able to draw quickly what this would look like it will be greatly appreciated

Thanks

Copakid

erikhaselhofer
Starting Member

30 Posts

Posted - 08/22/2013 :  22:08:02  Show Profile  Reply with Quote
There are all kinds of these around. I did a quick Google, which you've probably done, but this looked like it had a bunch.

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

As far as drawing I'm not sure how I would do that here. I don't think I can overlap smilies.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 08/23/2013 :  01:11:13  Show Profile  Reply with Quote
You can find and understand the visual representation of joins
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

--
Chandu
Go to Top of Page

Copakid
Starting Member

2 Posts

Posted - 08/25/2013 :  17:23:50  Show Profile  Reply with Quote
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 counts
DT1 4 records
CDY 4 records
B2C 4 records with 3 of them also in DT1
ACL – 4 records with 3 in DT1
AL – 10 records with 3 in DT1
Sm1 – 20 records with 1 in DT1

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.

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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 08/28/2013 :  03:39:03  Show Profile  Reply with Quote
i think u need to read this
http://www.sommarskog.se/query-plan-mysteries.html
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4608 Posts

Posted - 08/28/2013 :  12:10:19  Show Profile  Reply with Quote
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.

Edited by - Lamprey on 08/28/2013 12:11:09
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000