Re: JOIN on multiple conditions
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 11/22/04
- Next message: Mike Epprecht (SQL MVP): "RE: Help with query....."
- Previous message: franklinbruce: "Stored Procedure"
- In reply to: Joe Celko: "Re: JOIN on multiple conditions"
- Next in thread: Joe Celko: "Re: JOIN on multiple conditions"
- Reply: Joe Celko: "Re: JOIN on multiple conditions"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 22 Nov 2004 13:26:03 +0100
On Sun, 21 Nov 2004 17:17:56 -0800, Joe Celko wrote:
>>> Your query is not quite the same as Gunnar's. Gunnar had an inner
>join between T1 and T2; you transformed this in a cross join. <<
>
>He said that what he did was not right, so I went back to his narrative
>and implemented it, as specified.
Hi Joe,
I think you are confused with another thread. Gunnar said his query times
out, not that the returned results are incorrect.
>>> You are right that the infixed operators are not mandatory - but they
>do make this kind of error more visible. <<
>
>I was in another thread and I wondering about the effect of the infixed
>operators on newbies. You must use the OUTER JOIN syntax, of course,
>but if you learn only the INNER JOIN syntax does it change the way you
>attack a problem?
Maybe - and I wouldn't call this a bad thing. Most queries that need to be
written ARE just simple joins with simple join conditions and simple
filter conditions. More complicated queries should not be written by
newbies anyway.
(snip)
>But I can lose sight of n-ary relationships like "
>
>SELECT *
> FROM T1, T2, T3
>WHERE T1.a BETWEEN T2.b AND T3.c;
>
>In this very simple example, if I started with T1, I might have written:
>
> SELECT *
> FROM T1 INNER JOIN T2 ON T1.a <= T2.b;
I think you meant to write
SELECT *
FROM T1 INNER JOIN T2 ON T1.a >= T2.b;
>then added:
>
> SELECT *
> FROM T1 INNER JOIN T2 ON T1.a <= T2.b
> INNER JOIN T3 ON T2.b <= T3.c;
I think you meant to write
SELECT *
FROM T1 INNER JOIN T2 ON T1.a >= T2.b;
INNER JOIN T3 ON T1.a <= T3.c;
>I have an answer that hides the "between-ness" of the problem. Add more
>conditions on the individual tables and between T1 and T3 and it is not
>very neat.
>
>In a more complex situation, like a JPNF problem, you can have real
>problems in reading and writing the code.
I'll be the last one to suggest that the "old-style" join syntax for inner
joins should be removed. There will probably be cases where it results in
a more readable query. But for MOST real-world problems, the infixed join
operations tend to result in queries that are more readable and easier to
understand.
>Then consider the myth that join conditions have to be in ON clauses and
>search arguments (SARGs) have to be in WHERE clauses that new ACCESS
>programmers seem to believe, and you have some handicapped programmers.
I've never heard this myht before, but that's probably just me. Regulars
in this group all know that, for an INNER JOIN, it doesn't matter if a
condition is placed in the ON or in the WHERE clause. I do agree with the
general advise that MOST queries are easiest to understand and maintain if
join conditions are included in the ON clause and filter conditions in the
WHERE clause - but this is general advise; there will always be
exceptions.
>I am not sure that all those infixed operators I voted for were a good
>idea. A lot of them were created for completeness in the Stanards, but
>are so weird they are not implemented -- ever use an OUTER UNION?
I only have experience with SQL Server, and I don't think such an
operation is implemented (at least not in SQL Server 2000). What is it
supposed to do?
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Mike Epprecht (SQL MVP): "RE: Help with query....."
- Previous message: franklinbruce: "Stored Procedure"
- In reply to: Joe Celko: "Re: JOIN on multiple conditions"
- Next in thread: Joe Celko: "Re: JOIN on multiple conditions"
- Reply: Joe Celko: "Re: JOIN on multiple conditions"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
