SQL Joins
1. Introduction to Joins
Joins are used to combine rows from two or more tables in a relational database
based on a related column. A join operation allows you to query data from
multiple tables simultaneously. There are different types of joins, each serving a
specific purpose for handling how rows from different tables are combined.
2. Types of Joins
a. INNER JOIN
Purpose: The INNER JOIN returns records that have matching values in
both tables. If there’s no match, those rows are excluded from the result.
Use Case: When you want to get data that exists in both tables.
SQL Example:
SELECT Employees.Name, Departments.Name
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.ID;
This query fetches the names of employees and their corresponding
department names, but only for employees that belong to a department.
b. LEFT JOIN (or LEFT OUTER JOIN)
Purpose: The LEFT JOIN returns all records from the left table (the first
table in the query) and the matched records from the right table. If there’s
no match, NULL values are returned for columns from the right table.
Use Case: When you want all records from the left table and only the
matched records from the right table (even if no match exists).
, SQL Example:
SELECT Employees.Name, Departments.Name
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.ID;
This query returns all employee names and their corresponding department
names, including employees who do not belong to any department (with
NULL for department name).
c. RIGHT JOIN (or RIGHT OUTER JOIN)
Purpose: The RIGHT JOIN returns all records from the right table (the
second table in the query) and the matched records from the left table. If
there’s no match, NULL values are returned for columns from the left table.
Use Case: When you want all records from the right table and the matched
records from the left table (even if no match exists).
SQL Example:
SELECT Employees.Name, Departments.Name
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.ID;
This query returns all department names and their corresponding employee
names, including departments that do not have any employees (with NULL
for employee names).
d. FULL OUTER JOIN
Purpose: The FULL OUTER JOIN returns all records when there is a match in
either the left or right table. If there’s no match, the result will contain
NULL values for the missing side.
Use Case: When you want to retrieve all records from both tables, including
those with no matching counterpart.
1. Introduction to Joins
Joins are used to combine rows from two or more tables in a relational database
based on a related column. A join operation allows you to query data from
multiple tables simultaneously. There are different types of joins, each serving a
specific purpose for handling how rows from different tables are combined.
2. Types of Joins
a. INNER JOIN
Purpose: The INNER JOIN returns records that have matching values in
both tables. If there’s no match, those rows are excluded from the result.
Use Case: When you want to get data that exists in both tables.
SQL Example:
SELECT Employees.Name, Departments.Name
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.ID;
This query fetches the names of employees and their corresponding
department names, but only for employees that belong to a department.
b. LEFT JOIN (or LEFT OUTER JOIN)
Purpose: The LEFT JOIN returns all records from the left table (the first
table in the query) and the matched records from the right table. If there’s
no match, NULL values are returned for columns from the right table.
Use Case: When you want all records from the left table and only the
matched records from the right table (even if no match exists).
, SQL Example:
SELECT Employees.Name, Departments.Name
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.ID;
This query returns all employee names and their corresponding department
names, including employees who do not belong to any department (with
NULL for department name).
c. RIGHT JOIN (or RIGHT OUTER JOIN)
Purpose: The RIGHT JOIN returns all records from the right table (the
second table in the query) and the matched records from the left table. If
there’s no match, NULL values are returned for columns from the left table.
Use Case: When you want all records from the right table and the matched
records from the left table (even if no match exists).
SQL Example:
SELECT Employees.Name, Departments.Name
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.ID;
This query returns all department names and their corresponding employee
names, including departments that do not have any employees (with NULL
for employee names).
d. FULL OUTER JOIN
Purpose: The FULL OUTER JOIN returns all records when there is a match in
either the left or right table. If there’s no match, the result will contain
NULL values for the missing side.
Use Case: When you want to retrieve all records from both tables, including
those with no matching counterpart.
