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

User · Answer

Don rsquo t forget that in Oracle  provided the join key attributes are named the same in both tables  you can also write this as   select   from Table1 inner join Table2 using  ID     This also has the same query plan  of course

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The performance should be identical  but I would suggest using the join-version due to improved clarity when it comes to outer joins   Also unintentional cartesian products can be avoided using the join-version   A third effect is an easier to read SQL with a simpler WHERE-condition

User · Answer

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

User · Answer

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

User · Answer

They re both joins and where that do the same thing   Give a look at In MySQL queries  why use join instead of where

User · Answer

I have an addition to that good answer   That s what is defined as SQL92 and SQL89 respectively  there is no performance difference between them although you can omit the word INNER  using just JOIN is clear enough and in the simplest query you save 5 keyboard strokes now imagine how many strokes there are in big ones

User · Answer

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

User · Answer

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

User · Answer

They re both inner joins that do the same thing  one simply uses the newer ANSI syntax

User · Answer

As kiewik said  the execution plan is the same    The JOIN statement is only more easy to read  making it easier not to forget the ON condition and getting a cartesian product  These errors can be quite hard to detect in long queries using multiple joins of type   SELECT   FROM t1  t2 WHERE t1 id t2 some field    If you forget only one join condition  you get a very long to execute query returning too many records    really too many  Some poeple use a DISTINCT to patch the query  but it s still very long to execute   That s accurately why  using JOIN statement is surely the best practice   a better maintainability  and a better readability   Further more  if I well remember  JOIN is optimized concerning memory usage

User · Answer

The performance should be identical  but I would suggest using the join-version due to improved clarity when it comes to outer joins   Also unintentional cartesian products can be avoided using the join-version   A third effect is an easier to read SQL with a simpler WHERE-condition

User · Answer

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

User · Answer

In a scenario where tables are in 3rd normal form  joins between tables shouldn t change  I e  join CUSTOMERS and PAYMENTS should always remain the same    However  we should distinguish joins from filters  Joins are about relationships and filters are about partitioning a whole   Some authors  referring to the standard  i e  Jim Melton  Alan R  Simon  1993   Understanding The New SQL  A Complete Guide  Morgan Kaufmann  pp  11   12  ISBN 978-1-55860-245-8    wrote about benefits to adopt JOIN syntax over comma-separated tables in FROM clause    I totally agree with this point of view   There are several ways to write SQL and achieve the same results but for many of those who do teamwork  source code legibility is an important aspect  and certainly separate how tables relate to each other from specific filters was a big leap in sense of clarifying source code

User · Answer

Functionally they are the same as has been said  I agree though that doing the join is better for describing exactly what you want to do  Plenty of times I ve thought I knew how I wanted to query something until I started doing the joins and realized I wanted to do a different query than the original one in my head

User · Answer

They re logically identical  but in the earlier versions of Oracle that adopted ANSI syntax there were often bugs with it in more complex cases  so you ll sometimes encounter resistance from Oracle developers when using it

User · Answer

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

User · Answer

It is true that  functionally  both queries should be processed the same way  However  experience has shown that if you are selecting from views that use the new join syntax  it is important to structure your queries using it as well  Oracle s optimizer can get confused if a view uses a  join  statement  but a query accessing the view uses the traditional method of joining in the  where  clause

[sql] Inner join vs Where

Examples related to sql

Examples related to performance

Examples related to oracle