SQL & the JOIN Operator

Question

SQL & the JOIN Operator

Viewing 9 posts - 91 through 98 (of 98 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Jeff Moden (2/27/2011)
Isn't this a bit like the article you wrote on designing databases? While I agree with all the things you've stated, entire books have been written on how to join and all the caveates that occur when dupes are present, etc, ad infinitum. This article is a good introduction to joins... it's not the final word just like your introduction to designing databases isn't and wasn't intended to be the final word. Think about it... 😉

The parallels weren't lost on me, no. That's no reason not to comment though, is it?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

wagner crivelini SSC Eights! Points: 922 More actions · Answer 2

SQLkiwi (2/27/2011)
Jeff Moden (2/27/2011)
Isn't this a bit like the article you wrote on designing databases? While I agree with all the things you've stated, entire books have been written on how to join and all the caveates that occur when dupes are present, etc, ad infinitum. This article is a good introduction to joins... it's not the final word just like your introduction to designing databases isn't and wasn't intended to be the final word. Think about it... 😉
The parallels weren't lost on me, no. That's no reason not to comment though, is it?

Well, Paul, I agree with most of your observations, but Jeff is right: the purpose of this article is to introduce the newbie to a new world, SQL Joins.

If we were talking about motor vehicles instead of SQL, the articles could focus on strollers as well as in Ferraris. I'm focusing on stroller drivers here and I think it would be pointless for this audience to hear some many important details at this moment.

Paul White SSC Guru Points: 150467 More actions · Answer 3

wagner crivelini (2/28/2011)
Well, Paul, I agree with most of your observations, but Jeff is right: the purpose of this article is to introduce the newbie to a new world, SQL Joins.

Ok, I hope the criticisms were taken in the constructive spirit they were intended.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

robmullins SSC Enthusiast Points: 135 More actions · Answer 4

I was just going over this in my class, but you explain it much better! Great work! Out of curiosity, I have been hammered on calling it a 'relation' instead of a 'table'. I know the terms are somewhat interchangeable but is there a hard-fast rule for what we call them?

Thanks in advance.

wagner crivelini SSC Eights! Points: 922 More actions · Answer 5

rob

relation is a term we generally use in academic environment and I guess this was the word Ed Codd used when he define the basics for relational modeling.

in day-top-day business, we assume the word TABLE describing the same (or roughly the same) concept.

robmullins SSC Enthusiast Points: 135 More actions · Answer 6

Gotcha! So more of an Ivory Tower nomenclature. Thanks for the speedy clarification.

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 7

SQLkiwi (2/27/2011)
Jeff Moden (2/27/2011)
Isn't this a bit like the article you wrote on designing databases? While I agree with all the things you've stated, entire books have been written on how to join and all the caveates that occur when dupes are present, etc, ad infinitum. This article is a good introduction to joins... it's not the final word just like your introduction to designing databases isn't and wasn't intended to be the final word. Think about it... 😉
The parallels weren't lost on me, no. That's no reason not to comment though, is it?

Heh... you're absolutely correct. I read more into it and you were a whole lot nicer about it than that other fellow. Thanks, Paul.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike M - DBA2B Right there with Babe Points: 764 More actions · Answer 8

Excellent job, Wagner!

This reminds me of the first truly useful article on SQL that I found back in 199...7, I think. It was a beautifully detailed article describing normalization techniques to 3NF. This would have been a grand companion to it (all those years ago...) when I first started in the field. I plan to use this article when I explain these concepts to friends and coworkers who want to understand how JOINs work.

Bravo!

---Mike<G>

Todd.Everett Valued Member Points: 62 More actions · Answer 9

A real world application of cross join is to generate the results of applying databased rules to the row occurrences they apply to. For example, I have an application that tracks the set up of standard AD Groups for database schemas. I have the rules for these groups modeled in tables, and then cross join the rules with the schemas to generate a result set that applies each rule to each schema. I output this result set to a tracking table so I can track the set up of all these AD groups. This of course is only effective because there are a small number (less than 100) of schemas for which to manage a small number of AD groups (around 8 per schema).