SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Refresher on Joins


A Refresher on Joins

Author
Message
Craig A. Silvis
Craig A. Silvis
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 170

I found this to be an informative and helpful refresher, thanks!


Charles Kincaid
Charles Kincaid
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4901 Visits: 2384

Good refresher.

Our company policy is to NOT use table aliases. The VP in charge of development came down hard. You have to use them in self joins though.



ATBCharles Kincaid
Gemma Davies
Gemma Davies
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 127
I'm still confused with them. I never know when to use which, I just experiment!
Ram-425798
Ram-425798
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 69

Mr. Jambu Krishnamurthy!

Excellent article.

Regards,

Ramaswamy


Dennis Wells
Dennis Wells
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 1

Mr. Jambu

As a newbee, I enjoyed the Article, however when I attempted to get the results for the last example on the FULL OUTER JOINS, I am unable to obtain the results you are showing, please provide me with the necessary joins to accompolish the example, Thanks

Observe the query and output carefully...and then see if you can get the following output, which you will agree makes more sense.

t1.f1 t2.f1 t3.f1===== ===== =====6     6     67     7     NULL8     8     NULL9     9     NULL10    10    NULLNULL  11    NULLNULL  12    12NULL  13    13NULL  14    NULLNULL  15    NULL5     NULL  NULL4     NULL  NULL3     NULL  32     NULL  21     NULL  NULL

John Bonano
John Bonano
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 10
Dennis, to get the result set you need, you can do the following:

select a.f1, b.f1, c.f1
from t1 a
full join t2 b on a.f1 = b.f1
full join t3 c on c.f1 = ISNULL(b.f1, a.f1)

John
RonKyle
RonKyle
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11916 Visits: 3748
I once used a Cartesian product to create a report to show all our companies instructors and all classes. I then had to match this with another query so that there would be a check mark by the classes the instructors taught. That was several years ago and haven't needed one since, but that proved it's always good to know these things because you don't know what will come up.



Wayne West
Wayne West
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9080 Visits: 3705
I must know -- why did the VP decree that aliases is bad? I use them all the time in all sorts of systems, including payroll. They're nigh unto a neccessity IMO when building complex queries.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Brian Munier
Brian Munier
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1223 Visits: 80

I did some checking on the three proposed types of outerjoins with no data in a second table:
Note: If you want data from the second table when it exists, then you should use Q3

Q1: select * from aat1
where aat1.f1 not in (select aat2.f1 from aat2)

--Exception joins should better be implemented like this:

Q2: select * from aat1
where not exists (select 1 from aat2 where aat1.f1 = aat2.f1)

Q3: SELECT a.*
FROM aat1 a
LEFT JOIN aat2 b ON a.f1=b.f1
WHERE b.f1 IS NULL

Using DBCC FREEPROCCACHE between them, IN SQL Server 2000, Q1 and Q2 have identical execution plans and estimated costs based on the small tables provided: Cost 0.0417 using a Left Anti Semi Join. Note: I did put pks on the tables and the results were equivalent but the cost slightly higher without the pks (no surprise)

Q3 came in at the same cost of: 0.0417, but it used a Nested Loops Left outer Join.

These are small tables so I tried similar queries on two parent child tables with the parent table havin a two column pk, and the child table having a 3 column pk. Row counts: Parent table: 1707989 Child Table: 431251

Once again Q1 and Q2 had the same estimated cost and used the same execution plan with a join type of Merge Join/Left Anti-Semi Join Cost: 32.8 and Q3 came in cost of 33.7 using a Merge Join/Left Outer Join.

I have had a Microsoft consultant tell me not to use the NOT IN syntax, but honestly I haven't seen proof in the execution plans.

Oh well this is some food for thought.


Charles Kincaid
Charles Kincaid
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4901 Visits: 2384

We built some complex querries and the aliases were hard to remember as to what table was doing what. Now our querries are even longer.

I voted to break some of them up and process the result in code. That got changed to breaking them up and sticking them into stored procs.



ATBCharles Kincaid
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search