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 (+);
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 (+);
