Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Struggling with 4-5 table join syntax

Author  Topic 

lchriste
Starting Member

1 Post

Posted - 2009-08-21 : 17:08:23
I've been a SQL programmer for a long time, but haven't had to make the syntax switch to doing JOINs in the FROM clause (rather than in the WHERE clause) and I am really struggling to make that quantum leap.

I found this pithy nugget on one website....
More than 2 tables can participate in a join. This is basically just an extension of a 2 table join.
3 tables -- a, b, c, might be joined in various ways:

a joins b which joins c
a joins b and the join of a and b joins c
a joins b and a joins c

....but they didn't include any actual examples of doing this in SQL. I have looked all over the place and can't find many good examples of joining 3, 4, 5 or more tables in various ways using the "new" syntax--but I can do it in my sleep using the old WHERE clause method.

Does anyone know of any good books, websites or other resources that would have lots of good examples (hopefully with cogent explanations) to help me make this painful and admittedly long overdue transition?


Old Dog looking for New Tricks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 17:28:34
>> I have looked all over the place and can't find many good examples of joining 3, 4, 5 or more tables

Why look "all over the place"? Just look HERE Practically every post from any forum will have JOIN examples using the "new" syntax. And "new" would be in geologic terms.

Think of it this way:
as each table is JOINed in the from clause it adds to whole. Columns from all previous tables JOINed are available to correlate with columns on the table being JOINed.

Be One with the Optimizer
TG
Go to Top of Page

IncisiveOne
Starting Member

36 Posts

Posted - 2009-08-22 : 05:56:52
quote:
as each table is JOINed in the from clause it adds to whole. Columns from all previous tables JOINed are available to correlate with columns on the table being JOINed.


That's completely true for the "old" join syntax as well. Columns from all tables joined (via WHERE or FROM) are available, period; and can be correlated in the query, period.

Now for the cogent answer to the question ...

Cheers

Anything worth doing, is worth doing right.
Go to Top of Page

IncisiveOne
Starting Member

36 Posts

Posted - 2009-08-22 : 18:31:30
Ichriste

There is no difference whatsoever in what the Optimiser does, what jons are formed from either {WHERE} or {ON} syntax; the result is the same. From my perspective, [here's an example from a recent post here], I do not see much difference between:

SELECT policy.policy_base,
policy.policy_suffix,
policy.policy_state,
policy_information.column_x,
endorsement.column_y
FROM policy,
policy_information,
endorsement
WHERE policy.policy_base = policy_information.policy_base
AND policy.policy_suffix = policy_information.policy_suffix
AND policy.policy_base = endorsement.policy_base
AND policy.policy_suffix = endorsement.policy_suffix
AND ...

and:
SELECT  policy.policy_base,
policy.policy_suffix,
policy.policy_state,
policy_information.column_x,
endorsement.column_y
FROM policy
INNER JOIN policy_information
ON policy.policy_base = policy_information.policy_base
AND policy.policy_suffix = policy_information.policy_suffix
INNER JOIN endorsement
ON policy.policy_base = endorsement.policy_base
AND policy.policy_suffix = endorsement.policy_suffix
WHERE ...

The difference is in syntax only. There is a small advantage in that the each join is grouped in itself, and thus a slightly lesser chance of people forgetting one of the {ON|WHERE} clauses; but that's all.

Second, switching from the "old" to the "new" simply because it is "new" is not a good enough reason.

Given your history, it is unlikely that the syntax is the impediment to your understanding. Go ahead and ask further, more exact questions.

Cheers

Anything worth doing, is worth doing right.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-08-23 : 08:31:23
Note that in SQL 2005 and above, it is no longer possible to do outer joins in the where clause. The *= syntax was deprecated in SQL 2000 and is not valid SQL any longer. So that's a very valid reason for switching to the new syntax, unless you're never going to need an outer join

--
Gail Shaw
SQL Server MVP
Go to Top of Page

IncisiveOne
Starting Member

36 Posts

Posted - 2009-08-24 : 10:39:25
Accepted.

Outer joins are not deprecated in ANSI SQL, just in MS.

Outer joins are useful when the data "base" isn't, when it is a data heap (where you need to join to a non-existent row, the antithesis of a normalised database; where the modeler didn't know what the little circles on the crows feet meant), quite useless otherwise. For the sane, an outer join is a projection, not a fact. Sure, there are many data heaps out there.

Anything worth doing, is worth doing right.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-24 : 11:46:18
Thanks for the "cogent" answer. But the OPs actual question was simply does anyone know any good recources for plenty of examples...

I guess there was an implied question though - does the new syntax change the nature of how the tables are joined? I think we agree that no, it is just different syntax. All the principals the OP is already familiar with still applies. With outer joins, however, the new syntax removes some potential ambiguity as the to the developers intention. This is where you may see differences in the plans as well as the results.

quote:
Originally posted by IncisiveOne

Accepted.

Outer joins are not deprecated in ANSI SQL, just in MS.

Outer joins are useful when the data "base" isn't, when it is a data heap (where you need to join to a non-existent row, the antithesis of a normalised database; where the modeler didn't know what the little circles on the crows feet meant), quite useless otherwise. For the sane, an outer join is a projection, not a fact. Sure, there are many data heaps out there.

Anything worth doing, is worth doing right.



I am surprised to hear the opinion that outer joins are useless in a properly modeled database.

Be One with the Optimizer
TG
Go to Top of Page

IncisiveOne
Starting Member

36 Posts

Posted - 2009-08-24 : 12:16:19
quote:
I am surprised to hear the opinion that outer joins are useless in a properly modeled database.


Post an example (of a valid outer join in a properly modeled database), and I will respond.

Anything worth doing, is worth doing right.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-08-24 : 13:15:42
Definitely one of the sillier and more pointless requests I've seen on these forums.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-24 : 13:50:03
quote:
Originally posted by IncisiveOne

Post an example (of a valid outer join in a properly modeled database), and I will respond.


Ok, I'll bite because I'm curious to hear your response. I can't vouch that this satisfies your conditions but...Here is an example from Books Online - Using Outer Joins


To include all products, regardless of whether a review has been written for one, use an ISO left outer join. The following is the query:

USE AdventureWorks;
GO
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID


Be One with the Optimizer
TG
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-24 : 14:00:40
I'm sorry, but someone needs to say it... The statement made that outer joins are useless in a properly modeled database is simply absurd.


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-24 : 14:21:28
quote:
Originally posted by Vinnie881

I'm sorry, but someone needs to say it... The statement made that outer joins are useless in a properly modeled database is simply absurd.


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881


I agree with that!


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-08-24 : 14:29:49
"Sophomoric" is more accurate than "absurd".

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-24 : 14:38:08
IncisiveOne = Fabian Pascal
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-08-24 : 16:02:19
quote:
Originally posted by robvolk

IncisiveOne = Fabian Pascal


According to wikipedia, he's retired.
http://en.wikipedia.org/wiki/Fabian_Pascal
"He has retired from the technological industry and now does political commentary, specially on Middle East issues."






CODO ERGO SUM
Go to Top of Page

IncisiveOne
Starting Member

36 Posts

Posted - 2009-08-24 : 19:11:44
Hah !

I can see that you people have the tar and feathers ready, you're spoiling for a fight; you've already placed me in the same basket case as the famous (or infamous) Pascal, whether that is accurate or not. No one is going to learn anything with that sort of attitude and name-calling on board. I am here to answer the seekers, not to fight with the responders; the seeker has been answered, so I will decline the invitation to being raped and pillaged. I thought there might be some open debate, but that requires open minds. Clearly in this forum, people hang onto their fixed opinions, their closed minds, and attack anyone who doesn't agree. Thank God, in my universe, the principles (not principals) are not quite that fragile; in fact the testing and modulating makes them stronger. But hey, to each his own. Forgive me if I stay away from your fires.

TG. Well, a textbook example demonstrating an outer join is exactly the opposite of what is required, because its explicit purpose is to demonstrate an outer join, and by definition is not either (a) reasonable example for demonstrating the opposite [that the outer join is not required] or (b) anywhere near a real world example.

Goodnight

Anything worth doing, is worth doing right.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 08:18:48
quote:
Originally posted by IncisiveOne


TG. Well, a textbook example demonstrating an outer join is exactly the opposite of what is required, because its explicit purpose is to demonstrate an outer join, and by definition is not either (a) reasonable example for demonstrating the opposite [that the outer join is not required] or (b) anywhere near a real world example.




So let me see if I understand you. You say the example given isn't reasonable because it doesn't give you room to show a counter example that doesn't use an outer join?

As far as B is concerned, why is using an MS sample database not a real world example? I think it was a great choice of database since most DBAs should be familiar with it and it's typical of databases of that type that you would find in "the real world".



An infinite universe is the ultimate cartesian product.
Go to Top of Page

IncisiveOne
Starting Member

36 Posts

Posted - 2009-08-25 : 09:12:30
Nothing to do with MS or Oracle or MySQL. The request was for a real world example.

If I write a textbook on how to get herpes, technically speaking, it is not a good idea for someone else to use that example to demonstrate how NOT to get herpes; to truly understand how to avoid contracting herpes. For the author to demonstrate an outer join, he has to pick the situation that is abnormal to database theory, a projection of non-facts or possible facts, instead of a joining of facts stored in the database.

This statement is technically false:
quote:
To include all products, regardless of whether a review has been written for one, use an ISO left outer join
To include all products, regardless of whether a review has been written for one, and regardless of products sold, the price of fish, etc., one merely selects from Production. Period. No joins or projections required.

The technically accurate statement is:
To include all products and product reviews, and to leave white space for products with no reviews, use an ISO left outer join.

That, by definition, is a projection, a possibility, not a joining-of-facts. Perfectly good when you need to create a spreadsheet from the database, to be loaded into Excel or whatever. Not relevant for normal online reports or transaction processing.

Anyway, the point is empty, because it is limited to dealing with outer joins, and therefore there is no scope for dealing with the concept of not needing outer joins. I can't use a herpes example to demonstrate the value of non-herpes. If we are going to examine the concept of outer joins being ready evidence of poor modeling then we have to use an example of poor modeling; the existing outer join; the corrected model; the removal of the outer join. That's somewhat more switched on and expectant than "duh, here's an outer join, now remove it". Hilarious.

Anything worth doing, is worth doing right.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 09:27:12
quote:

Anyway, the point is empty, because it is limited to dealing with outer joins, and therefore there is no scope for dealing with the concept of not needing outer joins.


But you were asserting that outer joins are never necessary in a properly designed database. You never really defined what you meant by properly designed, I expected much quibbling here.

But before you even got there, you asked for a real example of useful outer join. It was given and you then complain that a legitimate example of outer joins was given rather than an example of a poor use of outer joins.

It's not like you're dealing with a bunch of dummies on this site. We can see through BS when it's presented.

Now if you have some real insight into why outer joins are useless, I'm all ears. But for crying out loud, stop trying to obfuscate and make excuses.





An infinite universe is the ultimate cartesian product.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-08-25 : 10:12:27
quote:
Originally posted by IncisiveOne

Hah !
I can see that you people have the tar and feathers ready, you're spoiling for a fight;


Actually, you are already boring me (except for that little bit of self-aggrandizing in your last post - nice touch). Welcome to the troll of the month club.
Yawn.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

IncisiveOne
Starting Member

36 Posts

Posted - 2009-08-25 : 10:20:08
"simply absurd"
"Sophomoric"
"IncisiveOne = Fabian Pascal"
"MS sample database"
Yeah, right.

Then we have complete inability to understand technical points, followed with:
"It's not like you're dealing with a bunch of dummies on this site".

Too many self-contradicting statements. Of course, it will be my fault. Sure. All the wars in the Middle East are my fault. You're all ears. Sure. I am the um obfuscator. Sure. If I do not jump when you say "cheese", it's making excuses. Hilarious. Thank God not everyone in this site is like that.

Anything worth doing, is worth doing right.
Go to Top of Page
    Next Page

- Advertisement -