Which comes first criteria or Join...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

its been a while since i have posted. I hope y'all doin fine.

I have a query which can be constrained two different ways. it can be
constrained by the join, or it can be constrained by criteria. i was
wondering which one hapens first and which one is prefered.

here are the details. i query a table which has three to five years worth of
data. the user enters the desired dates on a form (usually of the past year).
my query needs to reference another query which already has criteria of the
same dates. so my question is, do i gain by entering again the creteria in
this new query, or is it enough that i am joining it to the other query.

thanks,

sam
 
Hi,


The JOIN occurs before the WHERE.

In general, use the ON clause if the condition is about two tables, and the
WHERE clause if the condition is about only one table.


In JET, there is a digression from the standard SQL: if a condition in the
join implies only one table (like table1.f1=table1.f2 or like
table1.f1=444), it is moved to the WHERE clause. So, the Iqaluit Trauma (in
pubs):

SELECT authors.city
FROM authors LEFT JOIN titleauthor
ON authors.au_id=titleauthor.au_id
AND authors.city='Iqaluit'

from Jet, returns nothing (since no author is known to live in Iqaluit, in
pubs) while from MS SQL Server, it returns all the rows from authors.


So, with MS SQL Server, really, the ON clause is evaluated first, then, if
any, the WHERE clause is applied to the result of the join, but in JET, the
'intuitive' behavior is used (which is NOT accordingly to the standard) and
thus, the ON clause may move parts of its criteria in the WHERE clause,
without issuing any warning. The difference only matters if you use outer
join.






Hoping it may help,
Vanderghast, Access MVP
 
Michel said:
In JET, there is a digression from the standard SQL: if a condition in the
join implies only one table (like table1.f1=table1.f2 or like
table1.f1=444), it is moved to the WHERE clause. So, the Iqaluit Trauma (in
pubs):

SELECT authors.city
FROM authors LEFT JOIN titleauthor
ON authors.au_id=titleauthor.au_id
AND authors.city='Iqaluit'

from Jet, returns nothing (since no author is known to live in Iqaluit, in
pubs) while from MS SQL Server, it returns all the rows from authors.

So, with MS SQL Server, really, the ON clause is evaluated first, then, if
any, the WHERE clause is applied to the result of the join, but in JET, the
'intuitive' behavior is used (which is NOT accordingly to the standard) and
thus, the ON clause may move parts of its criteria in the WHERE clause,
without issuing any warning.
Click to expand...

Actually, the story is a lot simpler. If you tried the above query in
Jet you would get the error, "Join expression not supported". When you
were testing this in Jet you presumably got an empty resultset because
you changed the query to:

SELECT authors.city
FROM authors LEFT JOIN titleauthor
ON authors.au_id=titleauthor.au_id
WHERE authors.city='Iqaluit'

which returns the same on SQL Server and Jet i.e. an empty set.

Therefore, rather than a 'digression', in this regard Jet is a 'subset'
of the ANSI-92 standard.
In general, use the ON clause if the condition is about two tables, and the
WHERE clause if the condition is about only one table.
Click to expand...

I don't think that is the correct conclusion! I believe the reason for
the ANSI-92 syntax is to be able to differentiate between a null value
in a nullable column in the unpreserved table itself and the null that
is generated by the outer join because there is no matching row. This
was not possible with the ANSI-89 syntax ('extended equality'). Jet's
current implementation seems to behave as the ANSI-89 syntax.
The difference only matters if you use outer
join.
Click to expand...

Semantically, yes, but when it comes to *syntax* Jet is fussy about
certain criteria in the join condition e.g.

SELECT authors.city
FROM authors INNER JOIN titleauthor
ON authors.au_id=titleauthor.au_id
AND authors.city='Iqaluit';

similarly generates the "Join expression not supported" error. So maybe
your earlier advice, "use the... WHERE clause if the condition is about
only one table" related to Jet syntax rather than semantics!

Jamie.

--
 
Hi,


Have you tried with

.... ON (authors.au_id=titleauthor.au_id AND authors.city='Iqaluit' )


I am sure there is a way to run it, with Jet (I once did, at least), but I
cannot test, at this moment.

I don't remember Jet ever supported the *= and =* syntax.




Vanderghast, Access MVP
 
HI,


Can't test on Pubs at the moment, but the following works, in Northwind:


SELECT Orders.CustomerID
FROM Orders LEFT JOIN [Order Details]
ON ( Orders.OrderID = [Order Details].OrderID AND
Orders.CustomerID='Joe' )


Vanderghast, Access MVP
 
Michel said:
the following works, in Northwind:

SELECT Orders.CustomerID
FROM Orders LEFT JOIN [Order Details]
ON ( Orders.OrderID = [Order Details].OrderID AND
Orders.CustomerID='Joe' )
Click to expand...

Forget my earlier explanation. There is an even simpler one: for the
join expression to be supported in Jet it requires parens e.g. removing
them from your Northwind example:

SELECT Orders.CustomerID
FROM Orders LEFT JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID AND
Orders.CustomerID='Joe';

results in the error.

Just to clarify: as you said earlier, it behaves the same as when the
single table expression is relocated from the join expression to the
search condition and is indeed a deviation from the ANSI-92 standard.
Apologies for the distraction.

Jamie.

--
 
Hi Michel and Jamie,

thanks for your full blown answer. I liked your answer, even though i didn't
understand fully your discussion. i will try to read again and again, i am
sure i will get it.

gotta run, thanks to you all,

sam

Jamie Collins said:
Michel said:
the following works, in Northwind:

SELECT Orders.CustomerID
FROM Orders LEFT JOIN [Order Details]
ON ( Orders.OrderID = [Order Details].OrderID AND
Orders.CustomerID='Joe' )
Click to expand...

Forget my earlier explanation. There is an even simpler one: for the
join expression to be supported in Jet it requires parens e.g. removing
them from your Northwind example:

SELECT Orders.CustomerID
FROM Orders LEFT JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID AND
Orders.CustomerID='Joe';

results in the error.

Just to clarify: as you said earlier, it behaves the same as when the
single table expression is relocated from the join expression to the
search condition and is indeed a deviation from the ANSI-92 standard.
Apologies for the distraction.

Jamie.
Click to expand...
 
Back
Top