what is the difference between join keyword and inner join keyword in oracle sql?

Cici picture Cici · Apr 9, 2013 · Viewed 29.8k times · Source

I can't find documentations on the key word join but I saw examples on the web using it.

I was doing some experiment with it in Oracle hr schema, where I have table departments:

  • deparment_name
  • manager_id
  • location_id

A table employees:

  • first_name
  • employee_id

And table locations:

  • location_id
  • city

Query should return the department_name, first_name of the manager of the department, and the city where the department is located.

The code using the keyword join seem to return the some result in comparison to using the keyword inner join

Code with join:

select d.department_name, e.first_name,l.city
from departments d
   join employees e on d.manager_id=e.employee_id
   join locations l on d.location_id=l.location_id

Code with inner join:

select d.department_name, e.first_name,l.city
from departments d
   inner join employees e on d.manager_id=e.employee_id
   inner join locations l on d.location_id=l.location_id

Is there a difference between the two condition, or am I just happen to stumble on a situation where they return the same results?

Answer

Sebas picture Sebas · Apr 9, 2013

Query expressions 179 7.5 - joined table

3) If a qualified join is specified and a join type is not specified, then INNER is implicit.

  • Following Oracle Standards (9i onward), the INNER prefix is also optional. Before 9i, Oracle didn't follow ANSI rules, and didn't even support JOIN syntax.