Riven
Riven
SQL JOIN operation
A join is a SQL operation used to combine rows from two or more tables based on a related column between them. Joins are essential for querying data in relational databases, allowing you to extract meaningful information by correlating data stored in different tables.
Types of Joins
SQL provides several types of joins, each serving a different purpose:
- INNER JOIN
- LEFT JOIN (or LEFT OUTER JOIN)
- RIGHT JOIN (or RIGHT OUTER JOIN)
- FULL JOIN (or FULL OUTER JOIN)
- CROSS JOIN
- SELF JOIN
Let’s explore each type in detail with examples.
1. INNER JOIN
INNER JOIN returns only the rows that have matching values in both tables.
Syntax
Example
Consider two tables: Employees and Departments.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
DepartmentID INT
);
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
Sample Data
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', 1);
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'HR'),
(2, 'Engineering');
INNER JOIN Query
To get a list of employees along with their department names:
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Result:
| EmployeeName | DepartmentName |
|---|---|
| Alice | HR |
| Charlie | HR |
| Bob | Engineering |
2. LEFT JOIN (LEFT OUTER JOIN)
LEFT JOIN returns all the rows from the left table and the matched rows from the right table. If there’s no match, NULL values are returned for columns from the right table.
Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Example
Continuing from the previous example, let’s say we want to list all employees and their departments, including employees who might not belong to any department.
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Result:
| EmployeeName | DepartmentName |
|---|---|
| Alice | HR |
| Bob | Engineering |
| Charlie | HR |
If we add an employee without a department:
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID) VALUES
(4, 'David', NULL);
Re-running the LEFT JOIN query:
Updated Result:
| EmployeeName | DepartmentName |
|---|---|
| Alice | HR |
| Bob | Engineering |
| Charlie | HR |
| David | NULL |
3. RIGHT JOIN (RIGHT OUTER JOIN)
RIGHT JOIN returns all the rows from the right table and the matched rows from the left table. If there’s no match, NULL values are returned for columns from the left table.
Syntax
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Example
Let’s retrieve a list of all departments and any employees assigned to them:
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Result:
| EmployeeName | DepartmentName |
|---|---|
| Alice | HR |
| Charlie | HR |
| Bob | Engineering |
| NULL | Engineering |
If we add a new department with no employees:
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(3, 'Marketing');
Re-running the RIGHT JOIN query:
Updated Result:
| EmployeeName | DepartmentName |
|---|---|
| Alice | HR |
| Charlie | HR |
| Bob | Engineering |
| NULL | Marketing |
4. FULL JOIN (FULL OUTER JOIN)
FULL JOIN returns all rows from both tables, with NULLs in places where there is no match.
Syntax
SELECT columns
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;
5. CROSS JOIN
CROSS JOIN returns the Cartesian product of both tables, combining all rows from the first table with all rows from the second table.
Syntax
SELECT columns
FROM table1
CROSS JOIN table2;
6. SELF JOIN
A SELF JOIN is a join in which a table is joined with itself. It is often used to compare rows within the same table.
Syntax
SELECT a.columns, b.columns
FROM table a, table b
WHERE a.common_column = b.common_column;
Practical Use Cases for Joins
- Reporting: Generating comprehensive reports that combine data from multiple tables.
- Data Analysis: Analyzing relationships and correlations between different entities.
- Dashboards: Creating visualizations that require data from various sources.
- Data Migration: When consolidating or migrating data, joins help ensure relationships are maintained.