5

Is there a performance difference (or any other reason) to use a JOIN instead of a WHERE clause as per the code examples below:

ex1.

select *
from table_1, table_2
where table_1.id=table_2.id;

ex2.

select *
from table_1
    join table_2 on
    table_1.id = table_2.id;
HardLeeWorking
  • 195
  • 2
  • 13
  • 4
    The second version is the commonly used and standard syntax. The first is archaic. There is no reason to use commas in the `FROM` clause. – Gordon Linoff Jan 08 '16 at 12:18
  • 2
    Both are actually ANSI/SO standard syntax, but there's no reason to use version 1. Explicit join syntax (version 2) is easier to write (without errors), easier to read, and easier to convert to outer join if needed. – jarlh Jan 08 '16 at 12:20
  • Old (unsupported by now) versions of Oracle (9i,10g) actually had some bugs related to the explicit `JOIN` syntax when used in materialized views or regular views. But nowadays there really isn't any reason to stick to the old implicit joins. –  Jan 08 '16 at 12:26
  • 1
    In practice the performance would probably be about the same, as the query optimiser will step in and JOIN your data. However there are many other gains to using JOINs, as pointed out throughout this post. Larger, more complex queries could suffer a performance drop using the older style syntax. – David Rushton Jan 08 '16 at 12:26
  • What were the results of your own tests? – Dan Bracuk Jan 08 '16 at 12:47
  • I could not discern any difference between the two although my data sets are relatively small right now but they have the potential to grow significantly and I wanted to future proof them where possible. – HardLeeWorking Jan 08 '16 at 12:50
  • I remember a few cases in Oracle when the performance improved just by changing to the join syntax. But I can't put an example and they were very complex. There were at least ten joined tables and other things so It was too hard to read anyway. I assume that with simple execution plans the DB knows that you want a join. But put lots of tables with subqueries, funtions, groups by, analytics funtions and low selective indexes by and you could potentially fool the DB to create a bad execution plans. Or may by some clever optimization hacks inside only work with joins. – borjab Jan 08 '16 at 12:56

1 Answers1

6

You can refer: Bad habits to kick : using old-style JOINs(The link is for SQL Server but most of the points are valid for Oracle as well). Comma seperated JOINS are now discouraged. You should use the second format of JOINS as it is more readable and also it is highly recommended and widely used nowadays. And as far as performance is concerned there is no difference between the two syntax. Also the first format of JOINS (ie, the comma seperated) is pre - 1992 standard SQL join syntax.

Also in case of Oracle, when you had to use LEFT JOINS OR RIGHT JOINS then you had to use the old style of using (+) which was used to identify the table that is being outer joined to. So it was confusing and makes the query less readable. So the use of explicit JOINS(version 2 in your case) makes it more easier to understand and much readable.

Community
  • 1
  • 1
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • 2
    There is **no** performance difference between the two, not even a "very slight" one. –  Jan 08 '16 at 12:23
  • @a_horse_with_no_name:- Removed that. Thanks for pointing. – Rahul Tripathi Jan 08 '16 at 12:23
  • @RahulTripathi thanks for the link, looks like I need to update some code to make it more readable for the next person. – HardLeeWorking Jan 08 '16 at 12:25
  • thanks @GordonLinoff I work in Oracle environments and do not use SQL Server so I will bear that in mind. – HardLeeWorking Jan 08 '16 at 12:37
  • @GordonLinoff:- Agreed. Updated my answer to your points. Thanks – Rahul Tripathi Jan 08 '16 at 12:38
  • 1
    @RahulTripathi . . . The reason I make the point is because of what "a_horse_with_no_name" points out. Earlier versions of Oracle actually had some bugs with respect to optimizing joins (which isn't so surprising; they had decades of experience optimizing the old style syntax). These have been fixed, and Aaron's points mostly apply, but that can't be taken for granted. – Gordon Linoff Jan 08 '16 at 12:40