SQL JOIN - Types, Syntax and Examples

Level up your SQL skills today with our video on SQL JOINs. Watch now!

SQL JOINs are effective methods to merge rows of two or more tables that are capable of matching the columns in between. The article covers all kinds of JOINs: INNER, LEFT, RIGHT, and FULL JOINs, while illustrating their syntax along with some examples, making one perfect to learn the extraction of data through complex database structures.

What is JOIN in SQL?

SQL JOINs, as the name implies, are SQL clauses that aggregate rows from two or more tables using shared values.

It merges the records from two or more database tables after taking them into account.

When using a select statement to access one or more tables, SQL JOIN is typically utilised.

SQL JOIN Syntax

The most used technique for Joining data from several tables is SQL JOIN, often known as INNER JOIN.

When Joining data from these tables, the syntax is as follows:

SELECT Name, City, Marks, Phone_no
FROM Student_detail, Personal_detail
WHERE Student_details.Name=Personal_detail.Name;

SQL JOIN Example

Now let us understand SQL JOIN with the help of a detailed example:

Consider this table showing student details:

Name	Roll No.	Marks
Akshay	1	57
Charu	2	68
Disha	3	52
Eva	4	68
Himanshu	5	75
Jitesh	6	88

Below given is another table showing the personal details:

Name	City	Phone No	Email Id
Akshay	Jaipur	9543846521	[email protected]
Disha	Bombay	8532465892	[email protected]
Jitesh	Banglore	9684365125	[email protected]

Get 100% Hike!

Master Most in Demand Skills Now!

Now, by giving the following syntax, the two tables are JOINed together to form a new table that shows the employeeid, employeename, departmentid,departmentname, salary.

select Employee.EmployeeID,Employee.Name,Department.DepartmentID,Department.DepartmentName,Salary 

from Employee,Department

where Employee.DepartmentID=Department.DepartmentID;

The result of the above SQL JOIN syntax is given below:

Name	City	Marks	Phone No
Akshay	Jaipur	57	9543846521
Disha	Bombay	52	8532465892
Jitesh	Banglore	88	9684365125

SQL JOIN Types

There are different types of JOINs available in SQL:

Inner JOINs in SQL
Left JOINs in SQL
Right JOINs in SQL
FULL JOINs in SQL

Inner JOIN in SQL:

Inner SQL JOIN Query returns a value when there is a match in both tables. Inner JOIN is the most commonly used method when it comes to JOINing tables. The terms ‘Inner JOIN’ and ‘SQL JOIN’ are sometimes used interchangeably. Below given is the syntax for Inner JOIN. It is also the same syntax used in the case of SQL JOIN.

Syntax:

SELECT Column_list
FROM TABLE1
INNER JOIN TABLE2
ON Table1.ColName = Table2.ColName

Example:

Select Employee.EmployeeID,Employee.Name,Department.DepartmentID,Department.DepartmentName,Salary  from

Employee

INNER JOIN

Department 

on Employee.DepartmentID=Department.DepartmentID;

Left JOIN in SQL:

All data from the left table are returned by the left SQL JOIN query, which also matches the rows from the right table.Now, if there is no matching value on the right side, Left JOIN will return the value from the right side as NULL. The outcome of a left join is typically identical to that of a right join, with the exception that all of the rows from the left table are included.

Syntax:

SELECT column_name
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

Example:

Select Employee.EmployeeID,Employee.Name,Salary,Department.DepartmentID,Department.DepartmentName  from

Employee

LEFT JOIN

Department 

on Employee.DepartmentID=Department.DepartmentID;

Name	Roll No.	Address	Email Id
Akshay	1	Jaipur	[email protected]
Charu	2	NULL	NULL
Disha	3	Bombay	[email protected]
Eva	4	NULL	NULL
Himanshu	5	NULL	NULL
Jitesh	6	Banglore	[email protected]

Right JOIN in SQL:

Even if there are no matches in the left table, the right SQL JOIN query returns every row from the right table.Basically, the Right JOIN will return all the values from the right table along with the matched values of the left table. Also, if there is no matched value found, the outcome will be NULL

Syntax:

SELECT column_name
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

Example:

Select Employee.EmployeeID,Employee.Name,Salary,Department.DepartmentID,Department.DepartmentName  from

Employee

RIGHT JOIN

Department 

on Employee.DepartmentID=Department.DepartmentID;

Name	Address	Roll No.	Email ID
Akshay	Jaipur	1	[email protected]
Disha	Bombay	3	[email protected]
Jitesh	Banglore	6	[email protected]

FULL JOIN in SQL:

All of the rows from the left and right tables are returned by the full SQL JOIN query.The result set is created by combining all the rows of both tables. The FULL JOIN result is obtained by combining the outcomes of the LEFT JOIN and RIGHT JOIN.

Syntax:

SELECT column_name
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

Example:

Select Employee.EmployeeID,Employee.Name,Salary,Department.DepartmentID,Department.DepartmentName  from

Employee

FULL JOIN

Department

on Employee.DepartmentID=Department.DepartmentID;