RE: SQL 'Select AS' syntax

I agree, using the formal join syntax reduces ambiguity, since all selection criteria is nicely scoped to a specific task. Once you get comfortable with the syntax and formatting, it's really not a hardship to use the structured code.

As for performance, I believe that using the formal join syntax would tend to improve performance, since the developer's intent is more clearly expressed to the optimizer. I have also found that, in cases involving very large result sets being joined to another table, that moving selection criteria from the WHERE clause into the ON clause can reduce the number of rows that need to be joined. This can dramatically improve performance, in some cases.

Additionally, in the case of outer joins, where null is returned for T2 fields, having selection criteria in the where clause for T2.status='ACTIVE', would filter out all rows where T2.status was null. If instead that criteria were declared in the ON clause of the join, the test is performed before the join, allowing your final select to return nulls as expected.

Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of CRPence
Sent: Saturday, January 12, 2008 11:39 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL 'Select AS' syntax


With INNER JOIN the WHERE for join selection should always be the
same. Thus why the QMQRY output was and remains valid, from its
availability prior to the JOIN syntax.

I think some other responses were over-presumptive on what should be
expected when using WHERE clause to do joins, beyond the INNER JOIN.
Albeit I do not recall any more than the simples case of only the join
selection being specified. However...

For any other JOIN type [than INNER JOIN] it is probably best to be
in the habit of explicitly stating the JOIN ON, because the intent to
the query engine may become ambiguous when there are any more selections
than the between-file tests to effect the join. In such an ambiguous
request, the query engine is not obligated to operate in the way you
have assumed it will. The outcome could include a possibly changing
result set. For example in an OUTER JOIN, some WHERE clause selections
could limit the results prior to the join selection; e.g. because the
optimizer found an index that quickly limits the rows to be joined, and
because no join selection rules were explicitly stated there is no
requirement to order when the selection occurs.

So rather than as a "performance boost", make a habit of avoiding use
of the WHERE clause for join to prevent surprises; be explicit in your
intentions for the SQL request that is sent to the query engine.

Regards, Chuck