Does it matter in Oracle 10 SQL if restrictions are in the JOIN part or the WHERE?

Question

Hi All,

This is my first question post so be gentle. 

My question relates to Oracle SQL and ANSI joins

If i write a query as follows:
SELECT  *
  FROM  table1 a
  JOIN  table2 b ON a.id = b.id AND a.field1 > 4
                                            AND a.field2 = 'MY VALUE'

Would there be any performance difference if i put the non-join restrictions in the WHERE? Example:

SELECT  *
  FROM  table1 a
  JOIN  table2 b ON a.id = b.id 
 WHERE  a.field1 > 4
      AND  a.field2 = 'MY VALUE'

Further, if i was to write a larger script and use 6+ joins, would there be a performance difference if i put the non-join restrictions in the JOIN vs. WHERE (basically the same two examples as above, only with more joins)?

Thanks for your time,

J

TheMadProfessor · Accepted Answer

I don't believe your first syntax is correct - AFAIK, JOIN is solely for use to define the relationship between tables, not any other selection criteria.

However, to address your base question, in my experience I haven't seen any performance difference between JOIN and explicit WHERE clauses if the order of clauses is identical. 

SELECT *
FROM table1 a
JOIN table2 b ON a.id = b.id 
WHERE a.field1 > 4
AND a.field2 = 'MY VALUE'

should process just as efficiently as

SELECT *
FROM table1 a, table2 b
WHERE a.id = b.id 
AND a.field1 > 4
AND a.field2 = 'MY VALUE'

Personally, I prefer explicit WHEREs, especially when more than 2 tables are involved. It also allows me to completely control the order of the clauses, which can improve efficiency in some cases by eliminating larger numbers of potential matches early on before cross-table comparisions are considered. For example, by redoing the above query as

SELECT *
FROM table1 a, table2 b
WHERE a.field2 = 'MY VALUE' 
AND a.field1 > 4
AND a.id = b.id 

it's likely far fewer rows are going to pass the first two clauses, thus requiring a minimum of comparisons of table1 and table2 id values.

Trending News

Does it matter in Oracle 10 SQL if restrictions are in the JOIN part or the WHERE?

1 Answer