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
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)
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment