CODEWITHSUNDEEP
sqloracle-databasejoininner-join
Nisar
Nisar

Reputation: 6038

Is there alternative to inner join

Is there something different between this two codes

select a.firstname,
       a.lastname,
       b.salary,
       b.designation
from table a,
     table b
where a.id = b.id

and 

select a.firstname,
       a.lastname,
       b.salary,
       b.designation
from table a inner join table b on a.id = b.id

Upvotes: 3

Views: 14054

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Functionally, no, these specific queries are the same. The second is using ANSI join syntax, which has been the standard since SQL-92, and available in Oracle since 9i. Although both still work, there are some things you need to use ANSI syntax for when working with outer joins.

The new style is generally preferred, partly because the old style makes it possible to accidentally introduce cartesian products by omitting a join condition - which you can't do with join syntax (accidentally; you can still do a cross join if you really want to do that on purpose) and it arguably makes your join/filter intentions clearer. Many find it easier to read, but that's a matter of opinion so that aspect is off-topic.

Oracle recommend you use ANSI joins, at least for outer joins. You can see from plans and traces that Oracle actually still translates ANSI syntax into its old format internally, so as long as your query is correct (i.e. no missing conditions) then there is no performance difference; and the optimiser can still choose to use indexes based on the where clause, not just the on clauses.

Many people here would recommend that you use ANSI syntax from the start if you're new to Oracle. Of course, not everyone agrees; there's a reference here to a dissenting voice, for example.

Upvotes: 5

Related Questions