;

SQL JOIN

In this tutorial, we will learn how to use JOIN clause and join two or more table using SQL.

SQL JOIN Clause

  • JOIN Clause is used to Join two to more tables. It creates a set of rows in a temporary table.
  • JOIN Clause works on two or more tables but atleast they have one common column field and have a relation between them.
  • A statement or query can contain one or multiple join operations.

Example:

Let us consider three tables "Employee", "Department" and "Designation" for records 

Table Name: Employee

ID EmpName City Country Gender Salary DeptID DesigID
1 Shankar Delhi India male 25000 1 6
2 Sourabh Delhi India male 30000 1 2
3 Ranvijay Mumbai India male 15000 2 3
4 Kapil Noida India male 25000 3 4
5 Shalini Jaipur India female 18000 2 5
6 Rakesh Faridabad India male 23000 1 2
7 Akshay Mumbai India male 21000 2 4
8 Sarah New York US female 76000 3 1
9 Rocky Noida India male 28000 3 6

Table: Department

DeptID DepartmentName
1 IT
2 Finance
3 HR

Table: Designation

DesigID DesignationName
1 GM
2 Manager
3 CEO
4 Developer
5 Team Leader
6 Executive

Example Using JOIN Clause

For selecting an employee name, department name and designation name, a query will be:-

Example
SELECT EMP.EmpName,Dept.DepartmentName,Desg.DesignationName 
FROM Employee EMP 
INNER JOIN Department Dept ON EMP.DeptId=Dept.DeptID 
INNER JOIN Designation Desg ON EMP.DesignationId=Desg.DesigID
Output
EmpName DeptartmentName DesignationName
Shankar IT Executive
Sourabh IT CEO
Ranvijay Finance Manager
Kapil HR Developer
Shalini Finance Team Leader
Rakesh IT Manager
Akshay Finance Developer
Sarah HR GM
Rocky HR Executive

Different types of JOIN Clause

SQL have Different types of Join as Follow:-

  • INNER JOIN: Returns records When there is atleast one match on both the tables.
  • LEFT OUTER JOIN: Return all the records from the left table, and return matched records from the right table.
  • RIGHT OUTER JOIN: Return all the records from the right table, and return matched records from the left table.
  • FULL OUTER JOIN: Return all the records when there is a match either in a left table or right table.
  • CARTESIAN JOIN OR CROSS JOIN: Returns the Cartesian product of the sets of records from two or more joined tables.
  • SELF JOIN: It is a type of SQL join which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY.