Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

A Refresher on Joins Expand / Collapse
Author
Message
Posted Tuesday, April 24, 2007 7:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:58 PM
Points: 92, Visits: 170

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

 

Post #360572
Posted Tuesday, April 24, 2007 8:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962

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.



ATB

Charles Kincaid

Post #360584
Posted Tuesday, April 24, 2007 9:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 20, 2013 3:18 AM
Points: 253, Visits: 127
I'm still confused with them.  I never know when to use which, I just experiment!
Post #360604
Posted Tuesday, April 24, 2007 12:04 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 11, 2013 12:32 PM
Points: 86, Visits: 64

Mr. Jambu Krishnamurthy!

 

Excellent article.

Regards,

Ramaswamy

Post #360642
Posted Tuesday, April 24, 2007 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 1, 2007 4:07 AM
Points: 1, 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
Post #360650
Posted Tuesday, April 24, 2007 4:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 12, 2008 1:06 PM
Points: 1, 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
Post #360722
Posted Wednesday, April 25, 2007 6:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:27 AM
Points: 802, Visits: 1,983
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.


Post #360797
Posted Thursday, April 26, 2007 8:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
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.
Post #361169
Posted Thursday, April 26, 2007 11:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 4, 2012 11:13 AM
Points: 257, 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.

Post #361278
Posted Thursday, April 26, 2007 1:31 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962

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.



ATB

Charles Kincaid

Post #361338
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse