2

what is the difference between inner join and equi join in oracle and which of the following queries gives high performance?

select e.*,d.* 
from emp e,dept d
where d.DNAME='ACCOUNTING' and e.DEPTNO=d.DEPTNO --(equi join)

select e.*,d.*
from emp e
inner join dept d on e.deptno=d.deptno 
where d.dname='ACCOUNTING'; --(inner join)
Shannon Severance
  • 18,025
  • 3
  • 46
  • 67
nayar k
  • 41
  • 1
  • 2

3 Answers3

1

Equijoin is a join where you check if a value is equal to another. An inner join is an equijoin, and a join using a where clause with the "=" symbol is an equijoin. So your question is : is it faster to use where clause or inner join statement ? Well this question is answered here :

Inner join vs Where

Spoil : They used the same plan so they are no differences. Oh and in the second case, you can do this :

select e.*,d.*
from emp e
inner join dept d on e.deptno=d.deptno and d.dname='ACCOUNTING';

Hope it helps

WaLinke
  • 726
  • 2
  • 6
  • 25
  • I personally think it is bad style to put filter conditions (e.g. d.dname='ACCOUNTING') into the JOIN section. It may be not affect the result (it does if you for some reason change to and outer join) but it makes the query harder to read especially if there are filters on various tables because then the filter conditions are spread across all the different ON clauses. – fhossfel Jul 18 '17 at 23:49
  • I agree, I thought it was better to understand that the two versions are actually the same, but I use where statement too even on an inner join. I only put conditions that compare both tables after the on clause. – WaLinke Jul 18 '17 at 23:55
  • @fhossfel - as you noted yourself, in an outer join a filter on a single table may produce different results depending on whether it is included in the join condition or in a WHERE clause. So I don't quite understand your point. When writing an inner join, why is it bad style to put single-table filters in the join condition? I don't do it that way myself, but I view that as a matter of preference, not good vs bad style. –  Jul 20 '17 at 01:50
1

"Equi join" is an join that uses equality for the comparison operators. Some limit the term equi join to just inner joins (for example http://www.orafaq.com/wiki/Equi_join) and others will use the term for both inner and outer joins (for example https://stackoverflow.com/a/5471290/121544).

Both your example queries are equi joins and both are inner joins. What is different is the syntax, the first not being available until the SQL-92 standard. The optimizer should come up with the same plan in both cases. The explicit inner join helps with avoiding accidental cross joins.

A non-equi inner join:

 select x
 from X
 inner join Y
     on Y.BeginDate <= X.SomeDate
     and X.SomeDate < Y.EndDate
Shannon Severance
  • 18,025
  • 3
  • 46
  • 67
  • 1
    Equi join is subset of inner join? I thought an outer join where the join condition is an equality (such as `a.id(+) = b.id`, using Oracle syntax) is still an equi join, isn't it? –  Jul 18 '17 at 23:43
  • searching, equi join is not standardized. Some limit to just inner joins. Will update. – Shannon Severance Jul 18 '17 at 23:44
0

Both your examples are inner joins. (The "opposite" of inner is outer; both your queries are inner joins, none of them is an outer join.) "Equi" join means join where the join condition is the equality of values from columns from the joined tables. Both your examples are equi joins.

The difference between your examples is just the syntax - the first one uses the old, proprietary Oracle syntax (not recommended) and the second one uses the standard, modern ANSI (SQL Standard) syntax (recommended).

Also: your two queries are equivalent. The Optimizer will translate them to the same executable operation - so there is no performance difference.