SQL JOIN - Types, Syntax and Examples

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

Table of Content

• What are JOINs in SQL?
• SQL JOIN Syntax
• SQL JOIN Example
• SQL JOIN Types
• Conclusion

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 column_list
from table1
join table2
on table1.column_name=table2.column_name

SQL JOIN Example

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

Consider this table showing Employee  details:

Below given is another table showing the Department  details:

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:

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;

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

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;