Joins in oracle

                  SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. 

SELECT * FROM EMPLOYEES;

 SELECT * FROM DEPARTMENTS;

SELECT * FROM LOCATIONS;

Types of Joins :

   Equiv Join
   Outer Join
         Right Outer Join
         Left Outer Join
         Full Outer Join
   Inner Join
   Cross Join

Equiv Join :


SELECT
Employees.First_name
,    Employees.Last_name
,    Employees.Department_id
,    Departments.Department_name
,    Departments.Location_id
FROM
Employees,
Departments
WHERE
Employees.Department_id = Departments.Department_id;


Equiv Join by using table Alias name:

SELECT
E.First_name
,   E.Last_name
, E.Department_id
,   D.Department_name
, D.Location_id
FROM
Employees   E
, Departments D
WHERE
E.Department_id = D.Department_id;


select * from employees;


Course students

- - - - - - - -        - - - - - - - -
c_id    c_name s_name   c_id
- - - - - - - -        - - - - - - - -
10 Java name1 20
20 Oracle name2 10
30 dotnet name3 40
40 testing name4 30
50 php name5 60
- - - - - - - -        - - - - - - - -

Join condition : Students.c_id  = course.c_id

Join  :  It will give you matched records only.

- - - - - - - - - -
s_name c_name
 - - - - - - - - - -
name1      oracle
name2 Java
name3 testing
name4 dotnet  
 - - - - - - - - - -

Outer Join   :  It will give you matched records + unmatched records

Types :
 
       Right outer join
left outer join
full outer join


Right outer join

 - - - - - - - - -
s_name c_name
- - - - - - - - -
name1   oracle
name2 Java
name3 testing
name4 dotnet
  -     php
 - - - - - - - - -

Left outer join

 - - - - - - - - - - -
s_name c_name
- - - - - - - - - - -
name1      oracle
name2 Java
name3 testing
name4 dotnet
  name5        -
 - - - - - - - - - - -

Full outer join

 - - - - - - - - - - -
  s_name c_name
 - - - - - - - - - - -
name1      oracle
name2 Java
name3 testing
name4 dotnet
    -        php
name5 -
 - - - - - - - - - - -

Outer Join : Right Outer Join


SELECT
E.First_name
, E.Last_name
, D.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E
,    Departments D
WHERE
E.Department_id (+) = D.Department_id;



Outer Join  :  Left Outer Join


SELECT
E.First_name
, E.Last_name
, E.Department_id
, D.Department_name
, D.Location_id
FROM  
Employees E
, Departments D
WHERE
E.Department_id = D.Department_id(+);


Outer Join : Full Outer Join

         Discuss later


Inner Join :


Select * from Employees;

select
E1.Employee_id
, E1.First_name
, E1.Manager_id
, E2.First_name "MANAGER_NAME"
From
Employees E1
, Employees E2
Where
E1.Manager_id = E2.Employee_id;


Perform Join By using KEY WORDS and ON clause

We can written this above concepts by using following methods

  Instead of WHERE clause we can use ON clause
  No need to include + sign
  Use Following Key words


Key words:


     Join
  Right Outer Join
  Left  Outer Join
  Full  Outer Join
  Inner Join
  Cross Join


Join


SELECT
E.First_name
, E.Last_name
, E.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E   JOIN
Departments D
ON
E.Department_id = D.Department_id;


Right outer Join


SELECT
E.First_name
, E.Last_name
, E.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E RIGHT OUTER JOIN
Departments D
ON
E.Department_id = D.Department_id;


Left outer Join


SELECT
E.First_name
, E.Last_name
,  E.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E LEFT OUTER JOIN
Departments D
ON  
E.Department_id = D.Department_id;


Full outer Join


SELECT
E.First_name
, E.Last_name
,  E.Department_id
,  D.Department_name
,  D.Location_id
FROM
Employees E FULL OUTER JOIN
Departments D
ON
E.Department_id = D.Department_id;


Inner Join or Self Join


     Selecting data with in a table.

SELECT
E1.Employee_id
,  E1.First_name
,  E1.Manager_id
,  E2.First_name "MANAGER_NAME"
From
Employees E1 INNER JOIN
Employees E2
ON
E1.Manager_id = E2.Employee_id;


Cross Join


Cross join is a Cartesian product no of rows in the first table join with no of rows in the second table
Cartesian product is form when you ignore the where clause or valid join condition
Cross join is a wrong join
To avoid the cross join we use the where clause or valid join condition

select
E1.Employee_id
,   E1.First_name
,   E1.Manager_id
,   E2.First_name "MANAGER_NAME"
From
Employees E1 CROSS JOIN
Employees E2;

n1 x3
n2 x1
n3 x2

n1   x1
n1   x2
n1   x3
n2   x1
n2   x2
n2   x3
n3   x1
n3   x2
n3   x3


Data From 3 tables  :

Relation
      Employees and Departments table  - Department_id column.
Departments and Locations table - location_id column.

SELECT
E.First_name     ,
E.Last_name      ,
E.Department_id  ,
D.Department_name,
D.Location_id    ,
L.Street_address ,
L.city
FROM
Employees   E ,
Departments D ,
Locations   L
WHERE
E.Department_id = D.Department_id AND
D.Location_id   = L.Location_id;

If you are going to fetch data from 50 tables. You need to write 49 condition ( i.e. n-1 condition)


Additional Examples For Outer Joins


CREATE TABLE COURSE
                 (
                     CID    NUMBER(3)    ,
                     CNAME  VARCHAR2(20)
                  );

INSERT INTO COURSE VALUES (10,'ORACLE');
INSERT INTO COURSE VALUES (20,'DOTNET');
INSERT INTO COURSE VALUES (30,'JAVA');
INSERT INTO COURSE VALUES (40,'UNIX');
INSERT INTO COURSE VALUES (50,'TESTING');


SELECT * FROM COURSE;


CREATE TABLE STUDENTS
(
SNAME  VARCHAR2 (20),
CID    NUMBER (3)
);

INSERT INTO STUDENTS VALUES ('SENTHIL', 20);
INSERT INTO STUDENTS VALUES ('MURUGAPPAN', 10);
INSERT INTO STUDENTS VALUES ('RAMESH', 30);
INSERT INTO STUDENTS VALUES ('KARTHI', 40);
INSERT INTO STUDENTS VALUES ('RAVI', 60);

SELECT * FROM STUDENTS;

Equi Join


SELECT
S.SNAME  ,
    C.CID    ,
    C.CNAME
FROM
STUDENTS S ,
COURSE   C
WHERE
S.CID = C.CID;


OUTER JOIN TYPES:

  RIGHT OUTER JOIN
  LEFT  OUTER JOIN
  FULL  OUTER JOIN

RIGHT OUTER JOIN
   
     It Shows Matched as well as unmatched Records on Right side table
       [By including + on right side]


SELECT
S.SNAME  ,
C.CID    ,
C.CNAME
FROM
STUDENTS S ,
COURSE C
WHERE
S.CID (+) = C.CID;


LEFT OUTER JOIN

   It Shows Matched as well as unmatched Records on Left side table
   [By including + on Left side]


SELECT
S.SNAME ,
C.CID   ,
C.CNAME
FROM
STUDENTS S ,
COURSE C
WHERE
S.CID = C.CID (+);