Inner join vs Where

Question

Is there a difference in performance (in oracle) between

Select * from Table1 T1 
Inner Join Table2 T2 On T1.ID = T2.ID

And

Select * from Table1 T1, Table2 T2 
Where T1.ID = T2.ID

?

Answer 1

Although the identity of two queries seems obvious sometimes some strange things happens. I have come accros the query wich has different execution plans when moving join predicate from JOIN to WHERE in Oracle 10g (for WHERE plan is better), but I can't reproduce this issue in simplified tables and data. I think it depends on my data and statistics. Optimizer is quite complex module and sometimes it behaves magically.

Thats why we can't answer to this question in general because it depends on DB internals. But we should know that answer has to be 'no differences'.

Answer 2

If the query optimizer is doing its job right, there should be no difference between those queries. They are just two ways to specify the same desired result.

Answer 3

i had this conundrum today when inspecting one of our sp's timing out in production, changed an inner join on a table built from an xml feed to a 'where' clause instead....average exec time is now 80ms over 1000 executions, whereas before average exec was 2.2 seconds...major difference in the execution plan is the dissapearance of a key lookup... The message being you wont know until youve tested using both methods.

cheers.

Answer 4

No! The same execution plan, look at these two tables:

CREATE TABLE table1 (
  id INT,
  name VARCHAR(20)
);

CREATE TABLE table2 (
  id INT,
  name VARCHAR(20)
);

The execution plan for the query using the inner join:

-- with inner join

EXPLAIN PLAN FOR
SELECT * FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

-- 0 select statement
-- 1 hash join (access("T1"."ID"="T2"."ID"))
-- 2 table access full table1
-- 3 table access full table2

And the execution plan for the query using a WHERE clause.

-- with where clause

EXPLAIN PLAN FOR
SELECT * FROM table1 t1, table2 t2
WHERE t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

-- 0 select statement
-- 1 hash join (access("T1"."ID"="T2"."ID"))
-- 2 table access full table1
-- 3 table access full table2

Answer 5

In PostgreSQL, there's definitely no difference - they both equate to the same query plan. I'm 99% sure that's also the case for Oracle.

Answer 6

I don't know about Oracle but I know that the old syntax is being deprecated in SQL Server and will disappear eventually. Before I used that old syntax in a new query I would check what Oracle plans to do with it.

I prefer the newer syntax rather than the mixing of the join criteria with other needed where conditions. In the newer syntax it is much clearer what creates the join and what other conditions are being applied. Not really a big problem in a short query like this, but it gets much more confusing when you have a more complex query. Since people learn on the basic queries, I would tend to prefer people learn to use the join syntax before they need it in a complex query.

And again I don't know Oracle specifically, but I know the SQL Server version of the old style left join is flawed even in SQL Server 2000 and gives inconsistent results (sometimes a left join sometimes a cross join), so it should never be used. Hopefully Oracle doesn't suffer the same issue, but certainly left and right joins can be mcuh harder to properly express in the old syntax.

Plus it has been my experience (and of course this is strictly a personal opinion, you may have differnt experience) that developers who use the ANSII standard joins tend to have a better understanding of what a join is and what it means in terms of getting data out of the database. I belive that is becasue most of the people with good database understanding tend to write more complex queries and those seem to me to be far easier to maintain using the ANSII Standard than the old style.