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 12345»»»

SQL & the JOIN Operator Expand / Collapse
Author
Message
Posted Tuesday, October 06, 2009 11:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 5:46 PM
Points: 29, Visits: 211
Comments posted to this topic are about the item SQL & the JOIN Operator
Post #798928
Posted Wednesday, October 07, 2009 4:49 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711
Pretty good information!
Post #799047
Posted Wednesday, October 07, 2009 4:52 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711
Wagner,
You might want to mention in the:

"Excluding the Intersection of the Sets" section this is similar to

select... where not in (select... from table2) but it performs alot better w/ the join vs. where not in?

Just my 02c. Again, well done!

Mark
Post #799051
Posted Wednesday, October 07, 2009 6:06 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 07, 2012 9:23 AM
Points: 304, Visits: 716
Excellent article Wagner!
Thank you very much!


There's no such thing as dumb questions, only poorly thought-out answers...
Post #799095
Posted Wednesday, October 07, 2009 6:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:05 AM
Points: 2,553, Visits: 559
I always enjoy reading "back to basics" articles and this one is great - simple, detailed and comprehensive.







**ASCII stupid question, get a stupid ANSI !!!**
Post #799107
Posted Wednesday, October 07, 2009 7:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 11:45 AM
Points: 1, Visits: 32
With respect for your efforts in creating a very useful article, I have one item of criticism. The numerous, simple, grammatical errors throughout the article destroy readability, and authorial credibility.
Post #799126
Posted Wednesday, October 07, 2009 7:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 24, 2011 7:50 AM
Points: 41, Visits: 83
Excellent article.

You mentioned not knowing a real world application of the CROSS JOIN.

In my experience, this is typically used for creating test data.

Sometimes you need to test data in all sorts of different configurations. By using a cross join, you can set up the different parameters and try all combinations.

Additionally, if you are trying to create bogus data for a test environment, this is one way of taking data from different parts of the real data and generating new data that is not actually real.

In many cases, this type of join is used on temporary or memory based tables in a batch since the data it produces often needs to go through additional transformation and filtering before it is useful.

I guess, technically, it isn't used in a "real world" application, but it is used for real world issues.
Post #799132
Posted Wednesday, October 07, 2009 7:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 5:46 PM
Points: 29, Visits: 211
Hi, super, thanks for your comments ... (and thanks everybody for all other comments on this articles).

I feel it's rather risky to try to find a golden rule when it comes to performance. It depends on so many variables that I'd better test things on each particular case.

In both statements we are talking about here, we have operations that we are told to avoid... Either using comparions to NULL value when using the JOIN or using the NOT IN predicate when using the subquery.

I guess the real advantage in using the syntax I suggested in the article is that the SELECT statement can list fields from both tables when we use the JOIN.

Regarding performance, I was curious to check your suggestion and so I ran a "SET STATISTICS PROFILE ON;" to show performance on both statements.

To my surprise, the Total Subtree Cost on those queries were almost exactly the same same (difference was less than 1%)

0.0070812 for the JOIN
0.00706536 for the subquery

Sometimes RDBMs play a trick on us.

Post #799134
Posted Wednesday, October 07, 2009 7:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 7:07 AM
Points: 6, Visits: 64
Terrific article!! I was in a meeting yesterday where we were talking about this very topic!

I think that visualizing what is happening by use of the Venn diagrams is important -- nice inclusion in the article.
Post #799148
Posted Wednesday, October 07, 2009 7:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:39 AM
Points: 49, Visits: 538
Great article. Re cross joins, I used to use them when I had one table with a list of products, and one table with a list of start dates and end dates (52 rows = weeks of the year). A simple cross join gave me weekly buckets for each product (which we then used for sales & purchasing forecasting).

Rog
Post #799177
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse