Re: Better "Join" vs "Where" clause?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

There is an extreme case, famous, in MS SQL Server only though, and the
example is in BOL I think, where it uses a full outer join to make what is
called a 'UNION JOIN'.

A UNION JOIN, between tables A and B, is, pictorially, the result like:

|
A | nulls
|
--------+-------------------
|
nulls | B
|



MS SQL Server has no explicit syntax for it, but can use:


FROM a FULL OUTER JOIN b ON 1 = 2



to simulate it. So, you can even have a ON clause on a pure constant.


Now, I agree, this is in a large part 'just for the show', but sometimes, it
just relaxing to see such things.



Vanderghast, Access MVP



"David W. Fenton" <XXXusenet@xxxxxxxxxxxxxxxxxxx> wrote in message
news:Xns9BC5D6E86DDDDf99a49ed1d0c49c5bbb2@xxxxxxxxxxxxxxxx
"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in
news:e#3SFiZnJHA.1168@xxxxxxxxxxxxxxxxxxxx:

A possible reason is that it is shorter than the other
alternatives.

Er, what? Shorter only matters if you're hand-coding your SQL. The
query optimizer doesn't care if equivalent SQL statements are longer
or shorter.

Also,
in Jet, you may have problem with [] names to use the subquery
alternative.

Well, unless you use ANSI 92, in which case you can use () for your
derived tables, and not have the bracket problems.

Compare:

FROM a LEFT JOIN [b b] (ON a.ab=[b b].ab AND [b b].bb=cc)

it is shorter than

FROM a LEFT JOIN (SELECT * from [b b] WHERE bb=cc) AS x ON
a.ab=x.ab

which is itself closer to the point, shorter and less prone to
error than to bring the condition to the where clause.

I don't even understand the first SQL statement, to be honest.

With the condition brought to the where clause, I doubt the
optimizer will use the same execution plan.

I think that when they JOIN and the WHERE clause actually are
equivalent, they will optimize the same, though that's going to be
highly dependent on the database engine. Certainly every test I've
ever done comparing equivalent implicit and explicit joins with Jet
has optimized identically (and a few years ago, I spent quite a bit
of time testing that, since I had feared that JOINS were better
optimized than the equivalent WHERE clauses -- I was wrong in Jet).

Indeed, with all in the ON clause, including
the sub-query, you clearly make the join by 'touching only'
records where [b b].bb = cc; while with the where clause, your
optimizer ***may*** miss that short-cut, decide to "make" the join
and then decide to make the elaborated test on each row of the
(then possibly huge) result of the join... much less efficient. It
is a possibility, not something occurring in each and every cases.

Well, again, it's going to be highly dependent on the database
engine involved, and the type of join you're trying to implement.
Left/Right joins are harder to code as WHERE clauses than they are
as joins, because it usually takes more than one WHERE criterion to
say the same thing as the JOIN statement.

With Jet, the first solution works, while I am not sure the second
does, due to the [ ]. Sure, sure, why someone would use ill form
name in the first place... It just happens some people do.

I still stand that the syntax SHOULD be avoided, in Jet, though,
mainly if the condition implying just one table is the preserved
table of the outer join.

I never even knew that you could join on a constant. I still can't
wrap my head around the very concept, to be honest.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


.

Relevant Pages

  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... "SQL Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... Even if I were to bypass the Hibernate generated SQL and roll my own, that did not work at all, it returned all rows unfiltered. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Re: SQL Syntax
    ... I am coding SQL statements for some time but I never came across an in depth ... > LEFT OUTER JOIN TableB ON TableA.PK = TableB.PK ... > note that I've put the condition into the ON clause. ... >> LEFT OUTER JOIN TableB etc. ...
    (microsoft.public.sqlserver.datamining)
  • Re: SQL Design Method
    ... their SQL quotations.... ... Given that Jet does not have primary or secondary volumes, ... clause is at best redundant and possibly illegal. ... I also note that you have not specified a PRIMARY KEY constraint, ...
    (microsoft.public.access.tablesdbdesign)