SQL Question - Which comes first... (1 Viewer)

[SamDeMan]

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

 

[SamDeMan]

Hi all,

I posted this question on Microsofts Discussion Board and i got an answer. i would like to share it with you. if you would like to see the follow ups on this, please go to Microsoft Access Disscussion Groups and go the Query section, i posted my question on 9/18/2006 titled: Which comes first criteria or Join...


thanks,

sam

i am also pasting in here the first response:

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