Oracle: Which one of the queries is more efficient?

Question

Query 1

select student.identifier,
       id_tab.reporter_name,
       non_id_tab.reporter_name
from student_table student
    inner join id_table id_tab on (student.is_NEW = 'Y'
                                  and student.reporter_id = id_tab.reporter_id
                                  and id_tab.name in('name1','name2'))
    inner join id_table non_id_tab on (student.non_reporter_id = non_id_tab.reporter_id)

Query 2

select student.identifier,
id_tab.reporter_name,non_id_tab.reporter_name
from student_table student,
     id_table id_tab,
     id_table non_id_tab
    where student.is_NEW = 'Y'
    and student.reporter_id = id_tab.reporter_id
    and id_tab.name in('name1','name2')
    and student.non_reporter_id = non_id_tab.reporter_id

Since these two queries produce exactly same output,I am assuming they are syntactically same(please correct me if I am wrong). I was wondering whether either of them is more efficient that the other.

Can anyone help me here please?

You should compare explain plans, but they are probably the same. The first one is far more readable as it uses Ansi Joins. The use of ansi joins is highly recommended — vercelli, Sep 13 '16 at 07:47
As both are doing the same thing, there won't be any difference in efficiency. The second however uses the outdated, ancient and fragile implicit joins in the `where` clause which is no longer considered good style (the parentheses in the `ON` condition of the first query are useless btw) — , Sep 13 '16 at 08:02

Aleksej · Accepted Answer · 2016-09-13T08:04:20.213

1

I would rewrite it as follows, using the ON only for JOIN conditions and moving the filters to a WHERE condition:

...
from student_table student
    inner join id_table id_tab on ( student.reporter_id = id_tab.reporter_id )
    inner join id_table non_id_tab on (student.non_reporter_id = non_id_tab.reporter_id)
where student.is_NEW = 'Y'
  and id_tab.name in('name1','name2')

This should give a more readable query; however, no matter how you write it (the ANSI join is highly preferrable), you should check the explain plans to understand how the query will be executed.

edited Sep 13 '16 at 08:04

answered Sep 13 '16 at 07:52

Aleksej

22,443
5
33
38

I checked the plans for these queries,They are exactly same.Does this mean that no query is more efficient that other? Also,can you tell me why should I prefer ANSI joins (keeping readability aside)? – Swapnil Sep 13 '16 at 07:58
1

If the have the same plan. they do the same things, so their efficiency is exactly the same. About ANSI join, [here](http://stackoverflow.com/questions/18891148/oracle-joins-comparison-between-conventional-syntax-vs-ansi-syntax) you find something useful – Aleksej Sep 13 '16 at 08:02
This will not make any difference – Sep 13 '16 at 08:03

score 0 · Answer 2 · answered Sep 13 '16 at 08:06

In terms of performance, there should be no difference. Execution Plans created by the Oracle optimizer do not differ.

In terms of readability, joining tables inside the WHERE clause is an old style (SQL89). From SQL92 and higher, it is recommended to use the JOIN syntax.

Oracle: Which one of the queries is more efficient?

2 Answers2