JOIN in SQL

JOIN statement in SQL is used to combine rows from two or more tables based on a related column between them. This operation enables the retrieval of meaningful and consolidated information from multiple tables in a relational database. The key idea behind the JOIN statement is to match and combine rows that have similar values in a specified column.

Basic Syntax of SQL JOIN

The basic syntax of the SQL JOIN statement is as follows:

SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.column_name = table2.column_name;

• SELECT: The columns you want to retrieve.
• FROM: The main table you are querying.
• JOIN: Specifies that you are combining data from multiple tables.
• ON: Defines the condition for the join, indicating which columns from each table are related.

Types of SQL JOINs

There are several types of JOIN operations, each serving a specific purpose:

INNER JOIN

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

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all records from the left table and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table.

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for columns from the left table.

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

FULL JOIN (or FULL OUTER JOIN)

The FULL JOIN keyword returns all records when there is a match in either the left or the right table. If there is no match, NULL values are returned for columns from the table without a match.

SELECT column1, column2, ...
FROM table1
FULL JOIN table2 ON table1.column_name = table2.column_name;

Practical Example

Consider two tables: Employees and Departments.

Employees Table

EmployeeID	EmployeeName	DepartmentID
1	John	101
2	Jane	102
3	Mark	103

Departments Table

DepartmentID	DepartmentName
101	IT
102	HR
103	Marketing

Now, let’s use INNER JOIN to retrieve a list of employees along with their respective department names:

SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

EmployeeID	EmployeeName	DepartmentName
1	John	IT
2	Jane	HR
3	Mark	Marketing