Oracle Apps Technical Blog

eBIZ Technics. Powered by Blogger.

Tuesday, April 28, 2015

Oracle Joins

Type of joins
-------------
(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;
3. NATURAL JOIN
---------------
- NATURAL JOIN clause is based on all columns in the two tables that have the same name.
- It selects rows from the two tables that have equal values in all matched columns
- If the columns having the same names have different data types , an error is returned

In oracle 9i it is possible to let the join be completed automatically based on columns in the
two tables which have matching data types and names using the keywords NATURAL JOIN.

Eg:       SELECT deptno,dname,ename,empno
            FROM DEPT
            NATURAL JOIN EMP
                        OR
            SELECT deptno,dname,empno,ename
            FROM EMP , DEPT
            WHERE EMP.DEPTNO= DEPT.DEPTNO

Natural Joins with WHERE clause

            Additional restrictions on a natural join are implemented by using a WHERE clause. The
example

SELECT deptno,dname,ename,sal
FROM DEPT
NATURAL JOIN emp
WHERE deptno IN (20,30)

4. NATURAL JOINS WITH THE USING CLAUSE
---------------------------------------

- If several columns have the same names but the data types do not match, The NATURAL JOIN
clause can be modified with the USING clause to specify the columns that should be used for
an equijoin
- Use the USING clause to match only one column when more thatn one column matches
- Do not use table name or alias in the referenced columns
- The NATURAL JOIN and USING clauses are mutually exclusive.

- The columns referenced in the USING clause should not have a qualifier (table name or alias)
anywhere in the SQL statement

SELECT E.ENAME,D.DNAME
FROM EMP E JOIN DEPT D USING (Deptno)
WHERE Deptno=30;

error coz we can use alias or depts refered in join in either select or where clause

SELECT E.ENAME,D.DNAME
FORM EMP E JOIN DEPT D
USING (DEPTNO);
      or
SELECT ENAME,DNAME
FORM EMP , DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO

5. CREATING JOINS WITH THE ON CLAUSE
--------------------------------------
- The join condition for the natural join is basically an equi join of all columns with the same
name
- To specify arbitrary conditions or specify columns to join, the ON clause is used.
- The ON clause makes code easy to understand

Eg
            SQL> SELECT E.ENAME,E.EMPNO,D.DNAME
              2  FROM EMP E
              3  JOIN DEPT D
              4  USING (DEPTNO);
                                    or
            SQL> ed
              1  SELECT ENAME,DNAME
              2  FROM EMP
              3  JOIN DEPT
              4* USING (DEPTNO)


THREE WAY JOINS WITH THE ON CLAUSE
----------------------------------

SELECT EMPNO,ENAME,DNAME,SALGRADE
FROM EMP E
JOIN DEPT D
ON D.DEPTNO = E.DEPTNO
JOIN  DEPT1 D1
ON D1.DEPTNO=E.DEPTNO

JOINS COMPARING SQL to oracle syntax
----------------------------------------------------
ORACLE                                            SQL 1999
---------------------------------------------------
EQUI-JOIN                            NATURAL/INNER JOIN
OUTER-JOIN                                    LEFT OUTER JOIN
SELF-JOIN                            JOIN ON
NON-EQUI-JOIN                              JOIN USING
CARTESIAN PRODUCT                 CROSS JOIN
---------------------------------------------------

6. LEFT OUTER JOIN
-------------------

 SELECT  E.ENAME,E.DEPTNO,D.DNAME
 FROM EMP E
 LEFT OUTER JOIN DEPT D-> (+) will on this table
 ON (E.DEPTNO=D.DEPTNO)


                                    OR

  1  SELECT  E.ENAME,E.DEPTNO,D.DNAME
  2  FROM EMP E,DEPT D
  3* WHERE E.DEPTNO=D.DEPTNO(+)
                                    OR
  1  SELECT  E.ENAME,E.DEPTNO,D.DNAME
  2  FROM EMP E,DEPT D
  3* WHERE D.DEPTNO(+)=E.DEPTNO
           


7. RIGHT OUTER JOIN:
---------------------

   SELECT E.ENAME,E.DEPTNO,D.DNAME
   FROM EMP E->(+) will b on this table
   RIGHT OUTER JOIN DEPT D
   ON(E.DEPTNO=D.DEPTNO)
                       
                        or

  1  SELECT  E.ENAME,E.DEPTNO,D.DNAME
  2  FROM EMP E,DEPT D
  3* WHERE D.DEPTNO=E.DEPTNO(+)
SQL> /           

           
OUTER-JOIN:
-------------

-> To join n tables together, you need a minimum of n-1 join conditions. This rule may not
apply if your table has a concatenated primary key, in which case more than one column
is required to uniquely identify each row.
-> The operator is a plus sign enclosed in brackets and "it is placed on the side of the join
that is deficient in information" ie OUTER JOIN
This operator has the effect of creating one or more null rows, to which one or more row from
the nondeficient table can be joined.
Table.column = is the condition that joins the tables together
table.column(+)  is the outer join symbol, which can be placed on either side of the where
clause condition but not on both sides. place the outer join symbol follwing the name of the
column in the table without the matching rows.

- the outer join operator can appear on only one side of the expression the side that has
information missing. It returns those rows from one table that have no direct match in the
other table.
- A condition involving an outer join cannot use the IN operator or b linked to another condition
by the  OR operator.

Eg: SELECT E.ENAME,E.EMPNO,E.MGR,M.EMPNO,M.ENAME
FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO(+)

--The above query gives all the employee details whose empno = mgrno which implies they are
--managers and we want the details of all employee (empno) who have mgr no or not ie whether
-- that empno has mgr or not it displays all the empno's and if any empno doesnt have mgr then
-- implies that employee doesnt have manager like the empno=7839 ename =KING


8. FULL OUTER JOIN:
--------------------
SELECT E.ENAME,E.DEPTNO,D.DNAME
FROM EMP E
FULL OUTER JOIN DEPT D

ON (E.DEPTNO=D.DEPTNO)