LEARN JOINS IN SQL WITH EXAMPLES

Agenda

1 Introduction 6 Full Join

2 What is Joins in SQL? 7 Natural Join

3 Inner Join 8 Cross Join

4 Left Join 9 Self Join

5 Right Join

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
 Introduction

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Consider Two Tables

EMP_DEPT
EMPLOYEE

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Question
Write a query to display NAME, GENDER, AGE and MARKS of all employees whose
EMP_ID in the EMPLOYEE table is equal to the EMP_ID in the EMP_DEPT table.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
 What is JOINS
in SQL?

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Joins
A JOIN is a means for combining fields from two tables (or more) by using values common
to each.

Types of JOINS:-

1. Inner Join
2. Left Join
3. Right Join
4. Full Join
5. Natural Join
6. Cross Join

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
 INNER JOIN: Returns all rows when there
LEFT JOIN
is at least one match in BOTH tables.

LEFT JOIN: Return all rows from the left

table, and the matched rows from the
right table.

RIGHT JOIN: Return all rows from the

Table 1 Table 2 right table, and the matched rows from
the left table.

FULL JOIN: Return all rows when there is

a match in ONE of the tables.

INNER JOIN
FULL JOIN RIGHT JOIN
 INNER JOIN

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Join, Inner Join
Write a query to display all the details from EMPLOYEE and EMP_DEPT where the
value of EMP_ID in EMPLOYEE is equal to the value Of EMP_ID in EMP_DEPT.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
 LEFT JOIN

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Left Join, Left Outer Join
Write a query to display all the details from EMPLOYEE and common details from
EMP_DEPT by using left join keyword.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
 RIGHT JOIN

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Right Join, Right Outer Join
Write a query to display common details from EMPLOYEE and all the details from
EMP_DEPT by using right join keyword.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
 FULL JOIN

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Full Join, Full Outer Join
Write a Query to display all the details from EMPLOYEE and EMP_DEPT by using full
join keyword.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
 NATURAL JOIN

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Natural Join
A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you
based on the common columns in the two tables being joined.

A NATURAL JOIN can be an INNER join, a LEFT join, or a RIGHT join. The default
is INNER join.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Natural Join
Write a query to display all the details from EMPLOYEE and EMP_DEPT where the
value of EMP_ID in EMPLOYEE is equal to the value of EMP_ID in EMP_DEPT by
using natural join keyword.

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
 CROSS JOIN

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Cross Join
The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets
of records from the two or more joined tables.

EMPLOYEE EMP_DEPT

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Cross Join

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
 SELF JOIN

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Self Join
The SQL SELF JOIN is used to join a table to itself as if the table were two tables.

To join a table itself means that each row of the table is combined with itself and
with every other row of the table.

Syntax:-

SELECT a.column_name, b.column_name...

FROM table1 a, table1 b

WHERE a.common_field = b.common_field;

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Self Join
SELECT E1.EMP_ID, E2.NAME, E1.SALARY
FROM EMPLOYEE E1, EMPLOYEE E2 WHERE
E1.SALARY < E2.SALARY

EMPLOYEE E1 EMPLOYEE E2

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
 Thank You

Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited