GroTechMinds

GroTechMinds logo
different joins in sql

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

employee table

Department 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:

table1

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:

table3

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:

table5

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:

table6

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:

table8

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:

table9

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: 

table10

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:

table11

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:

table12

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:

table13

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:

table14

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:

table15

Problem Statement:

Find out the ename and loc.

Query:

select employee.ENAME, dept.loc

from employee cross join dept;

Output Screen:

table16

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

Popular Courses
Share:
Upskill Yourself
Consult Us