JOINS in Oracle

JOINS

Joins, the information from any number of tables can be accessed.

To join two tables, the retrieval criteria will typically specify the condition that a column in the first table (which is defined as a foreign key) is equal to a column in the second table (which is the prrimary key

Types of Joins
Oracle Proprietary Joins (8i and Prior) - Equi join -Cartesian Joins - Non-equi join - Outer Join - Self Join	SQL: 1999 Complaint Joins: - Cross Joins - Natural Joins - Using Clause Joins - Inner Joins - Full or two side outer joins - Joins with the ON Clause

Equi –Joins
When two tables are joined together using equality of values in one or more columns, they make an Equi Join.

This is also called as Simple Joins or Inner Joins
- Primary Key and Foreign Key
select emp.empno,emp.ename,dept.dname from emp, dept where emp.deptno=dept.deptno ;

Using AND Operator
select emp.empno,emp.ename,dept.dname from emp, dept where emp.deptno=dept.deptno and emp.deptno=20 ;

Using Table Aliases
select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno and
e.deptno=20 ;

Joining More Than two Tables
select e.empno,e.ename,d.dname, l.loc_Name from emp e, dept d, Locations l where e.deptno=d.deptno and d.location_id=l.location_id ;

Cartesian Joins

When no join condition clause is specified in WHERE clause, each row of one matches every row of the other table. This results in a Cartesian product.
SQL> select * from emp, dept ;

If the number or rows in emp Table is 14 and dept table has 4 , then the total number rows produced is 56.

Non-Equi-joins:
select e.name,e.sal,j.grade from emp e , salgrade j where e.sal between j.losal and j.hisal ;

Using (+) operator Outer-joins

If there are any values in one table that do not have corresponding value(s) in the other, in an euqi join that row will not be selected. Such rows can be forcefully selected by using the outer join symbol (+). The corresponding columns for that will have NULLs.

select emp.empno,emp.ename,dept.dname from emp, dept where emp.deptno (+) = dept.deptno ;
select emp.empno,emp.ename,dept.dname from emp, dept where emp.deptno = dept.deptno (+) ;

Self-joins

To Join a table itself means that each row of the table is combined with itself and with every other row of the table. The self join be viwed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as through it were.

select WORKER.ENAME, MANAGER.ENAME “Manager” from emp worker, emp manager where worker .mgr=manager.empno ;

Cross Join
select ename,dname from emp cross join dept ;
select ename,dname from emp, dept ;

Natural Join
- The NATURAL JOINS clause is based on all columns in the two tables that have same name

select deptno,dname,location_id,loc_name from dept natural join locations ;
select deptno,dname,location_id,loc_name from dept natural join locations where deptno in (10,20) ;

Creating Joins with the USING Clause
- Do not use a table name ot alias in the referenced columns
- The natural join and using Clauses are mutually exclusive
select l.loc_name , d.dname from locations l join dept d using (location_id) where location_id=100 ;

you can not write l.location_id = 1400
- same as equi joins

Creating Joins with the ON Clause
- The join condition for the NATURAL JOIN is basically an equijoin of all columns with the same name.

select e.empno,e.ename,e.deptno from emp e join dept d ON (e.deptno=d.deptno) ;
select e.ename, m.ename from emp e Join emp m on (e.empno=m.mgr) ;

Creating Three-Way Joins with the ON Clause
select e.empno,e.ename,d.dname,l.loc_name from emp e join dept d ON (e.deptno=d.deptno)
join locations L ON (d.location_id=l.location_id) ;

Inner Joins
Select e.ename,e.deptno,d.dname from emp e inner join dept d on (e.deptno=d.deptno) ;

Left Outer Joins
Select e.ename,e.deptno,d.dname from emp e left outer join dept d on (e.deptno=d.deptno) ;
earlier:
select emp.empno,emp.ename,dept.dname from emp, dept where dept.deptno (+)= emp.deptno ;

Right Outer Joins
select e.ename,e.deptno,d.dname from emp e right outer join dept d on (e.deptno=d.deptno) ;
earlier:
select emp.empno,emp.ename,dept.dname from emp, dept where dept.deptno = emp.deptno (+) ;

Full Outer Joins
select e.ename,e.deptno,d.dname from emp e full outer join dept d on (e.deptno=d.deptno) ;

Set Operator
Set Operators are used to combine information of similar type from one or more than one table
Data types of corresponding columns must be the same
The types of SET operators in ORACLE are:


Union: - Rows of first query plus rows of second query, less duplicate rows
union all - Rows of first query plus rows of second query, with duplicate rows
Intersection – Common rows frm all the queies
Minus – Rows unique to the first query

UNION
select * from emp union select * from emp1 ;
UNION ALL
select * from emp union all select * from emp1;

INTERSECT
select * from emp intersection select * from emp1;
MINUS
select * from emp minus select * from emp1;