SQL JOIN Tutorial

Introduction

SQL JOIN is a powerful feature that allows you to combine rows from two or more tables based on a related column between them. This tutorial will guide you through the different types of JOINs and how to use them effectively.

Types of JOINs

INNER JOIN

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

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

LEFT JOIN (or LEFT OUTER JOIN)

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

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side if there is no match.

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

FULL JOIN (or FULL OUTER JOIN)

The FULL JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records.

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

CROSS JOIN

The CROSS JOIN keyword returns the Cartesian product of the two tables, i.e., all possible combinations of rows.

SELECT columns
FROM table1
CROSS JOIN table2;

Examples

Example 1: INNER JOIN

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

Example 2: LEFT JOIN

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Example 3: RIGHT JOIN

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Example 4: FULL JOIN

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Example 5: CROSS JOIN

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;

Conclusion

Understanding SQL JOINs is crucial for working with relational databases. By mastering these techniques, you can efficiently query and analyze data from multiple tables.