Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 606
Cici Avatar asked Apr 09 '13 01:04

Cici


People also ask

Is join and inner join same in Oracle?

Difference between JOIN and INNER JOINJOIN returns all rows from tables where the key record of one table is equal to the key records of another table. The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns.

Is join and inner join the same?

SQL Inner Join clause is the same as Join clause and works the same way if we don't specify the type (INNER) while using the Join clause. In short, Inner Join is the default keyword for Join and both can be used interchangeably.

Which is better join or inner join?

'Inner join' is better, although it is equivalent to 'join' in performance as well as function.

What is the difference between natural join and inner join?

1. The join operation which is used to merge two tables depending on their same column name and data types is known as natural join. Inner joins have a specific join condition. Here, the join operation is used to form a new table by joining column values of two tables based upon the join-predicate.


1 Answers

  • Following 1992 ANSI SQL reference, INNER is optional:

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.
like image 99
Sebas Avatar answered Sep 18 '22 17:09

Sebas