Different Joins In SQL
After reading this blog you will be able to answer the questions mentioned below under 'Take Quiz' section
Introduction
The SQL Joins retrieve the data from a minimum of two tables. There are four main types of Joins in SQL:
Employee Table
Department Table
1) Inner Join
 Inner Join In SQL will fetch common data between the two tables or multiple tables. Values come in pairs and no null values exist in the inner join.Â
Syntax
Select t1.CN1,t2.CN2
From t1 inner join t2
On t1.cc = t2.cc;
Key
t1-left table name
t2-right table name
CN-column name
CC-common column
Problem Statement:
Find out the employee name and their dname.
Query:
select EMPLOYEE.ename, DEPT.dname
from EMPLOYEE inner join DEPT
on EMPLOYEE.DEPT_NO = DEPT.DEPT_NO;
Output Screen:
Problem Statement:
Find out the employee name and dname of those who belong to dept_30.
Query:
select EMPLOYEE.ename,DEPT.dname
from EMPLOYEE inner join DEPT
on EMPLOYEE.DEPT_NO = DEPT.DEPT_NO
where EMPLOYEE.DEPT_NO=30;
Output Screen:
Problem Statement:
Find out the employee name, dname, and location of those employees whose location starts with C.
Query:
select EMPLOYEE.ename, DEPT.dname, DEPT.loc
from EMPLOYEE inner join DEPT
on EMPLOYEE.DEPT_NO = DEPT.DEPT_NO
where DEPT.loc like ‘c%’;
Output Screen:
Problem Statement:
Find out the employee name, and location name for those employees who have the maximum salary.
Query:
select EMPLOYEE.ename, EMPLOYEE.salary, DEPT.loc
from EMPLOYEE inner join DEPT
on EMPLOYEE.DEPT_NO = DEPT.DEPT_NO
where EMPLOYEE.SALARY=(select max(salary)
                       from employee);
Output Screen:
2) Outer Join
  Outer joins are joins that return matched values and unmatched values from either or both tables. The outer join is of three typesÂ
a. Left Outer Join Â
Left Outer Join In SQL will fetch data that is common between the two tables and everything from the left table. Values come in pairs and there are null values in the left outer join.Â
Syntax
Select t1.CN1,t2.CN2
From t1 left outer join t2
On t1.cc = t2.cc;
Key
t1-left table name
t2-right table name
CN-column name
CC-common column
Problem Statement:
Find out the commission and dname using Left Outer Join.
Query:
select EMPLOYEE.comm,DEPT.dname
from EMPLOYEE left outer join DEPT
on EMPLOYEE.DEPT_NO = DEPT.DEPT_NO;
Output Screen:
Problem Statement:
Find out mgr, loc, and dname belong to dept_no=10.
Query:
select EMPLOYEE.mgr, DEPT.loc, DEPT.dname
from EMPLOYEE left outer join DEPT
on EMPLOYEE.DEPT_NO = DEPT.DEPT_NO
where employee.DEPT_NO=10;
Output Screen:
b. Right Outer Join  Â
Right Outer Join In SQL will fetch data that is common between the two tables and everything from the right table. Values come in pairs and there are null values in the right outer join.Â
Syntax
Select t1.CN1,t2.CN2
From t1 right outer join t2
On t1.cc = t2.cc;
Key
t1-left table name
t2-right table name
CN-column name
CC-common column
Problem Statement:
Find out the commission and dname Right Outer Join.
Query:
select EMPLOYEE.comm,DEPT.dname
from EMPLOYEE right outer join DEPT
on EMPLOYEE.DEPT_NO = DEPT.DEPT_NO;
Output Screen:
Problem Statement:
Find out mgr, loc, and dname belong to dept_no=10.
Query:
select EMPLOYEE.mgr, DEPT.loc, DEPT.dname
from EMPLOYEE right outer join DEPT
on EMPLOYEE.DEPT_NO = DEPT.DEPT_NO
where employee.DEPT_NO=10;
Output Screen:Â
c. Full Outer JoinÂ
Full Outer Join In SQL will fetch matched and unmatched records between the two tables.
Syntax
Select t1.CN1,t2.CN2
From t1 full outer join t2
On t1.cc = t2.cc;
Key
t1-left table name
t2-right table name
CN-column name
CC-common colum
Problem Statement:
Find out the ename and dname Full Outer Join.
Query:
select EMPLOYEE.ename, DEPT.dname
from EMPLOYEE full outer join DEPT
on EMPLOYEE.DEPT_NO = DEPT.DEPT_NO;
Output Screen:
Problem Statement:
Find out the commission and dname using Full Outer Join
Query:
select EMPLOYEE. comm, DEPT.dname
from EMPLOYEE full outer join DEPT
on EMPLOYEE.DEPT_NO = DEPT.DEPT_NO;
Output Screen:
3) Self Join
 Self Join in SQL when we join the table to itself.
Syntax
Select t1.CN1, t2.CN1
From employee t1, employee t2
Where t1.CN1 =t2.CN2
Key
t1-left table name
t2-right table name
CN-column name
CC-common column
Problem Statement:
Find out the ename and manager’s name.
Query:
select A.ename, B.ename
from EMPLOYEE A, EMPLOYEE B
where A.MGR =B.EMP_NO;
Output Screen:
Problem Statement:
Find out the ename and manager’s name of those employees whose name starts with S.
Query:
select A.ename, B.ename
from EMPLOYEE A, EMPLOYEE B
where A.MGR =B.EMP_NO and A.ename like ‘s%’;
Output Screen:
4) Cross Join
Cross Self Join in SQL each record is getting crossed with each other.
Syntax
Select t1.CN1, t2.CN1
From t1 cross join t2;
Key
t1-left table name
t2-right table name
CN-column name
Problem Statement:
Find out the ename and dname.
Query:
select employee.ENAME, dept.dname
from employee cross join dept;
Output Screen:
Problem Statement:
Find out the ename and loc.
Query:
select employee.ENAME, dept.loc
from employee cross join dept;
Output Screen:
Conclusion
SQL joins help put together information from different tables in databases. We can fetch data between two or more tables. We have four types of joins through which we can fetch common data, data from the left table, data from the right table, data from one table itself, and cross data with each other.
Test Your Knowledge
Upskill Yourself
Consult Us
Consult Us
