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


SQL & the JOIN Operator


SQL & the JOIN Operator

Author
Message
wagner crivelini
wagner crivelini
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 283
Comments posted to this topic are about the item SQL & the JOIN Operator
SuperDBA-207096
SuperDBA-207096
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4359 Visits: 711
Pretty good information!
SuperDBA-207096
SuperDBA-207096
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4359 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
blandry
blandry
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1727 Visits: 723
Excellent article Wagner!
Thank you very much!

There's no such thing as dumb questions, only poorly thought-out answers...
sushila
sushila
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15703 Visits: 639
I always enjoy reading "back to basics" articles and this one is great - simple, detailed and comprehensive.







**ASCII stupid question, get a stupid ANSI !!!**
jclark017
jclark017
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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.
Chris.Strolia-Davis
Chris.Strolia-Davis
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 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.
wagner crivelini
wagner crivelini
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 283
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. :-)
Janus Lin
Janus Lin
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 66
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.
roger_os
roger_os
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 568
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
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