Oracle Proprietary Joins SQL: 1999
(8i and Prior) Compliant Joins
------------------------ -----------------
- Equijoin - Cross Joins
- Non-Equijoin - Natural Joins
- Outer Join - Using Clause
- Self Join - Full or Two sided outer joins
- Arbitrary join conditions for outer joins
1. Use a join to query data from more than one table
SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2]|
[NATURAL JOIN table2] |
[JOIN table2 USING { column_name } ] |
[JOIN table2 ON (table1.column_name= table2.column_name)] |
[LEFT | RIGHT | FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name ) ];
2. Eg Cross join:
SELECT ename,dname
FROM emp
CROSS JOIN DEPT;
INNER JOIN
NATURAL JOIN
CROSS JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
When we join two tables or datasets together on an equality (i.e. column or set of columns) we are performing an inner join. The ANSI method for joining EMP and DEPT is as follows.
SQL> SELECT d.dname, d.loc, e.ename, e.job
FROM dept d
INNER JOIN
emp e
USING (deptno);
or
SELECT d.dname, d.loc, e.ename, e.job
FROM dept d
INNER JOIN
emp e
ON (d.deptno = e.deptno);
or
SELECT d.dname, d.loc, e.ename, e.job
FROM dept d
, emp e
WHERE d.deptno = e.deptno;
Natural Joins
A natural join will join two datasets on all matching column names, regardless of whether the columns are actually related in anything other than name. For example, the EMP and DEPT tables share one common column name and a natural join between the two tables would be correct in this scenario.
The following example converts our INNER JOIN from previous examples to a NATURAL JOIN.
SQL> SELECT d.dname, d.loc, e.ename, e.job
FROM dept d
NATURAL JOIN
emp e;
Note that the only identifiable benefit of NATURAL JOIN is that we do not need to specify a join predicate. Oracle determines all matching column names and uses these to join the two tables. We cannot alias any columns used in the natural join, as the following example demonstrates.
if two tables has same columns then it will fail ,then we have to use "INNER JOIN"
left outer join
In traditional Oracle syntax, outer joins are indicated by (+) and this can sometimes cause issues when attempting to outer join multiple tables or includeg expressions in join predicates. Oracle outer joins have no concept of direction, whereas ANSI-compliant outer joins do. In the following example, we will outer join DEPT to EMP using the ANSI LEFT OUTER JOIN. The way to interpret this is to read the tables in the FROM clause from left to right. The left-hand table is the superset and the table on the right is the potentially deficient set.
Note: The LEFT JOIN keyword returns all the rows from the left table (dept), even if there are no matches in the right table (emp).
SQL> SELECT deptno, d.dname, d.loc, e.ename, e.job
FROM dept d
LEFT OUTER JOIN
emp e
USING (deptno);
The OUTER keyword is optional but due to the lack of (+) symbols, including it seems to be more descriptive. Note that this example included the USING clause for our outer join predicates, but the ON clause would also work as well. The Oracle syntax for this join is as follows.
SELECT d.deptno, d.dname, d.loc, e.ename, e.job
FROM dept d
, emp e
WHERE d.deptno = e.deptno (+);
FULL Outer Join
The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).
The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
SQL FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
Self join
The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
SELECT e.empno,
e.ename,
e.job,
m.ename mgrname,
m.job mjob,
m.empno
FROM emp e, emp m
WHERE e.empno = m.mgr
