SQL & the JOIN Operator

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wagner Crivelini » SQL & the JOIN Operator

99 posts, Page 1 of 10

1 2 3 4 5 »»»

SQL & the JOIN Operator

Rate Topic

Display Mode

Topic Options

Author

Message

wagner crivelini

Posted Tuesday, October 06, 2009 11:25 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 10:57 AM
Points: 28, Visits: 173

Comments posted to this topic are about the item SQL & the JOIN Operator

Post #798928

SuperDBA-207096

Posted Wednesday, October 07, 2009 4:49 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711

Pretty good information!

Post #799047

SuperDBA-207096

Posted Wednesday, October 07, 2009 4:52 AM

UDP 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

blandry

Posted Wednesday, October 07, 2009 6:06 AM

Old 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

sushila

Posted Wednesday, October 07, 2009 6:35 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:44 AM
Points: 2,553, Visits: 513

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

jclark017

Posted Wednesday, October 07, 2009 7:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 9:28 AM
Points: 1, Visits: 30

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

Chris.Strolia-Davis

Posted Wednesday, October 07, 2009 7:13 AM

SSC 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

wagner crivelini

Posted Wednesday, October 07, 2009 7:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 10:57 AM
Points: 28, Visits: 173

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. Smile

Post #799134

Janus Lin

Posted Wednesday, October 07, 2009 7:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 17, 2012 1:19 PM
Points: 6, Visits: 63

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

roger_os

Posted Wednesday, October 07, 2009 7:57 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 10:30 AM
Points: 49, Visits: 529

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 »

99 posts, Page 1 of 10

1 2 3 4 5 »»»

Permissions