| 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 tablesWhy 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 OptimizerTG |
 |
|
|
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 ...CheersAnything worth doing, is worth doing right. |
|
|
|
IncisiveOne
Starting Member
36 Posts |
Posted - 2009-08-22 : 18:31:30
|
IchristeThere 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.CheersAnything worth doing, is worth doing right. |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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. |
|
|
|
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 OptimizerTG |
|
|
|
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. |
|
|
|
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.________________________________________________ |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-24 : 13:50:03
|
quote:
Originally posted by IncisiveOnePost 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;GOSELECT p.Name, pr.ProductReviewIDFROM Production.Product pLEFT OUTER JOIN Production.ProductReview prON p.ProductID = pr.ProductID Be One with the OptimizerTG |
|
|
|
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 |
|
|
|
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. |
|
|
|
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.________________________________________________ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-08-24 : 14:38:08
|
| IncisiveOne = Fabian Pascal |
|
|
|
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 |
|
|
|
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.GoodnightAnything worth doing, is worth doing right. |
|
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
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.________________________________________________ |
|
|
|
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. |
|
|
|
Next Page
|
Struggling with 4-5 table join syntax