SQL join clause is used to combine rows from two or multiple tables based on a common columns between them.

There are several types of Join, like:

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

Before discussing these topics, we will create another table named empsalary. Follow the screenshot.

Now input some data like this screenshot.

Lets continue the topics.

Left Join

The LEFT JOIN keyword returns all records from the left table, and the matched records from the right table. The result shows NULL in the right side, if there is no match.

Pattern:

SELECT table1.column1,table1.columnN,table2.column2,table2.columnN
FROM table1
LEFT JOIN table2
on table1.column1=table2.column1

Example:

Query to show employee name, department, payment month, year, and salary amount.

SELECT emp.`Emp_Id`,emp.`FirstName`,emp.`Department`,salary.`Month`,Salary.`Year`,salary.`Salary` 
FROM employees AS emp
LEFT JOIN empsalary AS salary
ON emp.`Emp_Id`=salary.`Emp_Id`;

#emp and #salary is used as alias instead of column name

Right Join

The RIGHT JOIN keyword returns all records from the right table, and the matched records from the left table. The result shows NULL from the left side, when there is no match.

Pattern:

SELECT table1.column1,table1.columnN,table2.column2,table2.columnN
FROM table1
RIGHT JOIN table2
on table1.column1=table2.column1

Example:

Query to show employee name, department, payment month, year, and salary amount.

SELECT emp.`Emp_Id`,emp.`FirstName`,emp.`Department`,salary.`Month`,Salary.`Year`,salary.`Salary` 
FROM employees AS emp
RIGHT JOIN empsalary AS salary
ON emp.`Emp_Id`=salary.`Emp_Id`;

Inner Join

The INNER JOIN keyword shows records that have matching values in both tables.

Pattern:

SELECT column(s) from table1
inner join table 2
on table1.column_name=table2.column_name;

Example:

Query to show employee name, department, payment month, year, and salary amount.

SELECT emp.`Emp_Id`,emp.`FirstName`,emp.`Department`,salary.`Month`,Salary.`Year`,salary.`Salary` 
FROM employees AS emp
INNER JOIN empsalary AS salary
ON emp.`Emp_Id`=salary.`Emp_Id`;

Full Outer Join

The FULL OUTER JOIN keyword returns all records when there is a match in left or right table records.

Pattern:

SELECT column(s) from table1
full join table 2
on table1.column_name=table2.column_name;

Self Join

A self JOIN is a normal inner join, but the table is joined with itself. In the table, a column value is related with another table column value. Self join is used very rarely.

Cross Join

Cross join is used when needs to show record for all combination.

Pattern:

SELECT column(s) from table1,table2;