Home » Other » General » Difference between Equi Join and Outer Join
Difference between Equi Join and Outer Join [message #112019] Tue, 22 March 2005 08:07 Go to next message
shounakbhattacharya
Messages: 7
Registered: March 2005
Location: India
Junior Member


What is the difference between Equi Join and Outer Join?

Thanx
Re: Difference between Equi Join and Outer Join [message #112117 is a reply to message #112019] Wed, 23 March 2005 02:24 Go to previous message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
From the Oracle9i SQL Reference:

An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns. Depending on the internal algorithm the optimizer chooses to execute the join, the total size of the columns in the equijoin condition in a single table may be limited to the size of a data block minus some overhead. The size of a data block is specified by the initialization parameter DB_BLOCK_SIZE.
SELECT last_name, job_id, departments.department_id, department_name
   FROM employees, departments
   WHERE employees.department_id = departments.department_id;

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
SELECT d.department_id, e.last_name
   FROM departments d LEFT OUTER JOIN employees e
   ON d.department_id = e.department_id
   ORDER BY d.department_id;

DEPARTMENT_ID LAST_NAME
------------- -------------------------
           10 Whalen
           20 Hartstein
           20 Fay
           30 Raphaely
...
          250
          260
          270
Users familiar with the traditional Oracle outer joins syntax will recognize the same query in this form:
SELECT d.department_id, e.last_name
   FROM departments d, employees e
   WHERE d.department_id = e.department_id(+)
   ORDER BY d.department_id;

At http://tahiti.oracle.com, you can find the Oracle manuals. The site requires a free subscription (it's worth it)

MHE

[Updated on: Wed, 23 March 2005 02:25]

Report message to a moderator

Previous Topic: What is host string?
Next Topic: Reg: Oracle 10g Installation
Goto Forum:
  


Current Time: Mon Sep 20 12:50:43 CDT 2021