Join Codition

[krishnaindia2007]

When I use left or right outer joins the query exectuion speed is very high when compared to join codition? Examples I have given below

1) Left outer join
select a.xx ,b.yy from
(
select xx,zz from table1
) a
left outer join
(
select yy,zz from table2
) b
on
a.zz = b.zz
2) Join
select a.xx,b.yy from table1, table2
Where a.zz = b.zz


Is there any difference in the way they execute these two queries? Why Outer join execution speed is high when compared to join codition?

[susarlasireesha]

An equijoin combines rows that have equivalent values for the specified columns. Depending on the internal algorithm the optimizerchooses 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;