SQL Joins

What are Joins?

SQL Joins are used to combine rows from two or more tables based on a related column between them. Joins allow you to retrieve data from multiple tables in a single query, making it easier to work with complex datasets.

Types of Joins

There are several types of joins in SQL, including:

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): 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.
• RIGHT JOIN (or RIGHT OUTER JOIN): 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.
• FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table records. If there is no match, NULL values are returned for the missing side.
• CROSS JOIN: Returns the Cartesian product of two tables, i.e., it combines all rows of the first table with all rows of the second table.

INNER JOIN Example

Here’s an example of how to use an INNER JOIN to retrieve data from two tables:

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

This query retrieves the first name and last name of employees along with their respective department names by matching the DepartmentID in both tables.

LEFT JOIN Example

To retrieve all employees along with their department names (including those without a department), you can use a LEFT JOIN:

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

This query returns all employees, and if an employee does not belong to any department, the DepartmentName will show as NULL.

Practice Exercise

Try creating your own queries using different types of joins. Here’s a hint for a FULL JOIN:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;