Inner join vs Where

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

?

Solution 1:

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

Solution 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.

Solution 3:

They should be exactly the same. However, as a coding practice, I would rather see the Join. It clearly articulates your intent,

Solution 4:

Using JOIN makes the code easier to read, since it's self-explanatory.

There's no difference in speed(I have just tested it) and the execution plan is the same.

Solution 5:

[For a bonus point...]

Using the JOIN syntax allows you to more easily comment out the join as its all included on one line. This can be useful if you are debugging a complex query

As everyone else says, they are functionally the same, however the JOIN is more clear of a statement of intent. It therefore may help the query optimiser either in current oracle versions in certain cases (I have no idea if it does), it may help the query optimiser in future versions of Oracle (no-one has any idea), or it may help if you change database supplier.