Better "Join" vs "Where" clause?

[Bob Barrows]

Outer join with a condition in the on clause implying only one table.
The result differs even among Jet versions (and patches):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID AND
authors.City = 'Iqaluit'



as example. With Jet 3.5, you got no record, but with MS SQL Server,
you get all records from authors.

Interesting. All of my attempts to use that syntax in Jet had failed
(syntax error), forcing me to resort to using a subquery to get that effect:

.... LEFT JOIN (select authorid from books where City = 'Iqaluit') as q
ON authors.authorID = q.authorID

Are you sure your example statement doesn't raise an error in Jet?

There is also the case of update through joins, which is immediately
available with JET, but with proprietary syntax for MS SQL Server (or
explicitly over a view).

I know about the different update/delete syntax - I thought we were
discussing the join syntax itself ... in select statements. David? is this
what you were referring to? If so, I withdraw my objection to your statement
except to say that it isn't the join syntax per se that is causing the
problems in this case: it's the different syntax required for update/delete
statements in T-SQL.

Not linked to join, but you cannot use ALIAS over alias in MS SQL
Server, while you can in Jet:

SELECT price * 1.06 AS TPS, (price + TPS) * 1.045 AS PST, price
+TPS + PST AS totalPrice FROM somewhere

I always thought that worked in Access because Access rewrote the query
before passing it to Jet. I'll have to try executing a similar sql statement
via ADO to see if that's the case.

 

[Michel Walsh]

Try, in Northwind:

------------------------------------
SELECT Orders.ShipCity
FROM Orders LEFT JOIN [Order Details]
ON (Orders.OrderID = [Order Details].OrderID
AND (((Orders.ShipCity)="Iqaluit")));
-----------------------------------

KEEP the parenthesis !


Jet returns no record (as per today state of the art of Jet 4.0 patches,
using Access 2003)

While MS SQL Server returns all the records from orders, ... even those
where shipCity is not equal to Iqaluit, that is.

MS SQL Server is right, since a left join should preserve all records from
the left table, but that is very COUNTER INTUITIVE.

Jet is more intuitive, wrong (by the SQL standard), but acceptable ... by
many (kind of)


Vanderghast, Access MVP

 

[Bob Barrows]

Ah, the parentheses are the key!

Try, in Northwind:

------------------------------------
SELECT Orders.ShipCity
FROM Orders LEFT JOIN [Order Details]
ON (Orders.OrderID = [Order Details].OrderID
AND (((Orders.ShipCity)="Iqaluit")));
-----------------------------------

KEEP the parenthesis !


Jet returns no record (as per today state of the art of Jet 4.0
patches, using Access 2003)

While MS SQL Server returns all the records from orders, ... even
those where shipCity is not equal to Iqaluit, that is.

MS SQL Server is right, since a left join should preserve all records
from the left table, but that is very COUNTER INTUITIVE.

Jet is more intuitive, wrong (by the SQL standard), but acceptable
... by many (kind of)


Vanderghast, Access MVP

 

[David W. Fenton]

David W. Fenton wrote:
[]

Examples, please.

Sure, they're easy enough to find in BOL ... let's see ... ah,
here we go - it's long so I'm putting it at the bottom

Uh, you're quoting examples of T-SQL. You haven't shown that the
results returned are different from Jet than within SQL Server.

Just because different databases optimize a SQL statement
differently does not mean they return different results.

It isn't - I should have said "... the *= syntax for performing
outer joins implicitly in the
WHERE clause in SQL Server ..."

So, er, um, what is your point here?

I'm lost as you seem to be talking about SQL Server alone, rather
than some difference in the result sets between the equivalent SQL
statements using implicit vs. explicit joins in Jet.

AFAIK one cannot perform an outer join in Access without using the
explicit JOIN syntax.

And....????

Frankly, I didn't read the long quotation in any detail. What I did
read didn't seem relevant to the discussion. Perhaps you could point
out an actual example of Jet SQL with equivalent implicit vs.
explicit joins that returns a different result.

That *was* what we were talking about, right?

 

[David W. Fenton]

Outer join with a condition in the on clause implying only one
table. The result differs even among Jet versions (and patches):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID
AND authors.City = 'Iqaluit'

I wouldn't call this a JOIN. It's also something I'd never think of
writing, as it's putting a WHERE clause criterion in the JOIN
clause. Looks like a really bad practice to me. If written thus, I'd
expect it to return the same results in both Jet and SQL Server (and
every other db engine):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID
WHERE authors.City = 'Iqaluit'

The implicit join version:

SELECT authors.*
WHERE (authors.authorID = books.authorID OR books.authorID Is Null)
AND authors.City = 'Iqaluit'

(I *think* that's correct)

If the result sets returned by the implicit join and explicit join
versions are different, then the implicit join has not been properly
written, seems to me.

 

[Bob Barrows]

I wouldn't call this a JOIN.

Why not? it's equivalent to this, which is certailnly a join:

SELECT authors.*
FROM authors LEFT JOIN
(select authorid from books where City = 'Iqaluit') as books
ON authors.authorID = books.authorID

It's also something I'd never think of
writing, as it's putting a WHERE clause criterion in the JOIN
clause. Looks like a really bad practice to me. If written thus, I'd
expect it to return the same results in both Jet and SQL Server (and
every other db engine):

See Michel's post. It doesn't.

 

[Michel Walsh]

since City is a field from authors, not from books,

(select authorid from books where City = 'Iqaluit')


is not making sense. It would if the condition was implying the unpreserved
table, but it is not the case, here.


Vanderghast, Access MVP

 

[Bob Barrows]

David W. Fenton wrote:
[]

And so far as I know, SQL Server also optimizes implicit joins
identically to explicit ones. In fact, it seems to me that
because of Jet's oddball join syntax, it might be more portable
to use implicit joins, since there's a lot more in common in
terms of WHERE clauses than JOINs.

You may have a point with inner joins, but with outer joins,
using the JOIN syntax vs the WHERE syntax can cause different
results to be returned.

Examples, please.

Sure, they're easy enough to find in BOL ... let's see ... ah,
here we go - it's long so I'm putting it at the bottom

Uh, you're quoting examples of T-SQL. You haven't shown that the
results returned are different from Jet than within SQL Server.

I didn't intend to say it was: at the time I made the above statement, I was
under the mistaken impression that Jet did not allow the syntax the BOL
article discussed, but Michel has definitely shown that the syntax is
allowed and the results are different.

Just because different databases optimize a SQL statement
differently does not mean they return different results.

Nothing to do with optimization: order of evaluation is the issue that
causes different results to be returned.

Frankly, I didn't read the long quotation in any detail. What I did
read didn't seem relevant to the discussion. Perhaps you could point
out an actual example of Jet SQL with equivalent implicit vs.
explicit joins that returns a different result.

See Michel's post - I won't repeat it.

But my introducing this subject has caused us to veer away from the reason I
stepped into this thread in the first place: you have yet to provide a
single example of a join statement that works in Jet but not in SQL Server.
If you're talking about the different syntax for update/delete statements
rather than the join structures themselves, then say so and I will drop out
of the discussion.

 

[Bob Barrows]

since City is a field from authors, not from books,

(select authorid from books where City = 'Iqaluit')


is not making sense. It would if the condition was implying the
unpreserved table, but it is not the case, here.

My mistake, I should have written

SELECT authors.*
FROM (select * from authors where City = 'Iqaluit') as authors
left JOIN
books ON authors.authorID = books.authorID

I was thinking your example was equivalent to the one shown in BOL (which
illustrated the differences in results when applying the filter to the
unpreserved table). I now see that it wasn't.

This is an interesting topic, albeit nothing to do with the reason I stepped
into this thread in the first place. it appears that Jet is evaluating the
statement like this:

SELECT authors.*
FROM (select * from authors where City = 'Iqaluit') as authors
left JOIN
books ON authors.authorID = books.authorID

while SQL Server is evaluating it like this:

SELECT authors.*
FROM authors Left Join
(select a.authorid from authors a inner join books b
on a.authorid = b.authorid where a.City='lqaluit') as q

I'm no expert on ANSI, so I'm not sure which is the behavior specified by
that standard. When you said "... MS SQL Server is right, since a left join
should preserve all records from the left table ... " did you mean "correct"
in terms of the ANSI specification?

 

[Michel Walsh]

A Bob hints it, there are alternative syntax, sure, but some are simply
shorter.

Compare

SELECT a.*, b.*
FROM a LEFT JOIN b ON (a.ab=b.ab AND b.bb='bb')


and


SELECT a.*, c.*
FROM a LEFT JOIN (SELECT * FROM b WHERE b.bb='bb') AS x
ON a.ab=x.ab




As per today, both returns the same result, in both Jet and MS SLQ Server.



NOTE that you have to be very careful about the details:


SELECT Orders.ShipCity
FROM Orders LEFT JOIN [Order Details]
ON (Orders.OrderID = [Order Details].OrderID
AND [Order Details].Quantity = -1);


return the same (similar) result, with both, Jet and MS SQL Server, but



SELECT Orders.ShipCity
FROM Orders RIGHT JOIN [Order Details]
ON (Orders.OrderID = [Order Details].OrderID
AND [Order Details].Quantity = -1);




does not ( at least, today ). Jet returns no record, while MS SQL Server
returns as many records as there is in Orders.



Vanderghast, Access MVP

 

[Michel Walsh]

Yes, MS SQL Server does it accordingly to the ANSI specs, while JET does
not. While MS SQL Server and Jet indeed differ on this statement, today
(that may change in another Office patch), with Jet, we must conclude that
this syntax has to be avoided... Although it is possible that someone cut
and paste the statement from MS SQL Server into Jet ... well, that someone
is better to be aware of the *possible* difference in result, even if no
'parsing' error occurs!


Vanderghast, Access MVP

 

[Bob Barrows]

Uh, you're quoting examples of T-SQL. You haven't shown that the
results returned are different from Jet than within SQL Server.

Quick recap:
We were discussing why it would be better to put JOIN criteria in the
JOIN clause rather than the WHERE clause. I had suggested that it would
be better to get in the habit of doing it in the JOIN clause so one
would be be better prepared to make the move to SQL Server. I supported
that contention by explaining that putting outer join criteria in the
WHERE clause in SQL Server (not possible in Jet) could produce different
results than putting the criteria in the JOIN clause. That's the only
point I was attempting to make.

 

[David W. Fenton]

you have yet to provide a
single example of a join statement that works in Jet but not in
SQL Server. If you're talking about the different syntax for
update/delete statements rather than the join structures
themselves, then say so and I will drop out of the discussion.

I explained this many posts ago -- I encountered the problem more
than once (it was a couple years ago during a SQL Server upsizing
project, attempting to convert some queries to SQL views, and the
joins in the Jet SQL had to be rewritten to work on SQL Server), but
I can't recall the exact syntax that caused the problem.

It *did* happen and I do recall that it was a problem with the
parens.

But as I said several posts ago, I can't provide the specifics as
they are lost in the mists of time.

If you would like to believe that this never happened to me, then so
be it.

 

[David W. Fenton]

Quick recap:
We were discussing why it would be better to put JOIN criteria in
the JOIN clause rather than the WHERE clause. I had suggested that
it would be better to get in the habit of doing it in the JOIN
clause so one would be be better prepared to make the move to SQL
Server. I supported that contention by explaining that putting
outer join criteria in the WHERE clause in SQL Server (not
possible in Jet) could produce different results than putting the
criteria in the JOIN clause. That's the only point I was
attempting to make.

And so far, you haven't done that, so far as I can see.

 

[David W. Fenton]

While MS SQL Server and Jet indeed differ on this statement, today
(that may change in another Office patch), with Jet, we must
conclude that this syntax has to be avoided... Although it is
possible that someone cut and paste the statement from MS SQL
Server into Jet ... well, that someone is better to be aware of
the *possible* difference in result, even if no 'parsing' error
occurs!

I'm having some difficulty figuring out *why* someone would every
code the problematic syntax.

 

[Michel Walsh]

A possible reason is that it is shorter than the other alternatives. Also,
in Jet, you may have problem with [] names to use the subquery alternative.
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.

With the condition brought to the where clause, I doubt the optimizer will
use the same execution plan. 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.


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.





Vanderghast, Access MVP

 

[David W. Fenton]

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.

 

[Michel Walsh]

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

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.