SQL Joins

By /

SQL joins are used to combine rows from two or more tables based on a related column between them. They are essential for querying relational databases and retrieving comprehensive data sets from multiple tables.

Types of SQL Joins:

  1. INNER JOIN
  2. LEFT (OUTER) JOIN
  3. RIGHT (OUTER) JOIN
  4. FULL (OUTER) JOIN
  5. CROSS JOIN
  6. SELF JOIN

1. INNER JOIN

Purpose: Returns only the rows that have matching values in both tables.

Let’s have a look at the below figure to get a better understanding:

Syntax:

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

Example:

SELECT Students.StudentID, Students.Name, Departments.Dept
FROM Students
INNER JOIN Departments
ON Students.StudentID = Departments.StudentID;

This query retrieves student details along with their respective department names, but only for those students who are assigned to a department.

2. LEFT (OUTER) JOIN

Purpose: Returns all rows from the left table, and the matched rows from the right table. If no match is found, NULLs are returned for columns from the right table.

Let’s have a look at the below figure to get a better understanding:

Syntax:

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

Example:

SELECT Students.StudentID, Students.Name, Departments.Dept
FROM Students
LEFT JOIN Departments
ON Students.StudentID = Departments.StudentID;

3. RIGHT (OUTER) JOIN

Purpose: Returns all rows from the right table, and the matched rows from the left table. If no match is found, NULLs are returned for columns from the left table.

Let’s have a look at the below figure to get a better understanding:

Syntax:

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

Example:

SELECT Students.StudentID, Departments.Dept, Students.Name
FROM Students
RIGHT JOIN Departments
ON Students.StudentID = Departments.StudentID;

4. FULL (OUTER) JOIN

Purpose: Returns all rows when there is a match in either left or right table. Rows without a match in one of the tables will contain NULLs for columns from that table.

Let’s have a look at the below figure to get a better understanding:

Syntax:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

Example:

SELECT Students.StudentID, Students.Name, Departments.Dept
FROM Students
FULL OUTER JOIN Departments
ON Students.StudentID = Departments.StudentID;

5. CROSS JOIN

Purpose: Returns the Cartesian product of both tables, i.e., each row from the first table is combined with all rows from the second table.

Let’s have a look at the below figure to get a better understanding:

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Example:

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

This query returns a combination of every student with every department.

6. SELF JOIN

Purpose: Joins a table to itself. Useful for hierarchical or recursive relationships within a single table.

Syntax:

SELECT a.columns, b.columns
FROM table a, table b
WHERE a.common_column = b.common_column;

Example:

SELECT A.StudentID AS StudentID_A, A.Name AS Name_A, B.StudentID AS StudentID_B, B.Name AS Name_B
FROM Students A
JOIN Students B
ON A.StudentID < B.StudentID;

Practical Scenarios for SQL Joins

  • INNER JOIN: Fetching employees and their corresponding departments where each employee is assigned to a department.
SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName FROM Employees INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
  • LEFT JOIN: Retrieving all employees and their departments, including those without an assigned department.
SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName FROM Employees LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
  • RIGHT JOIN: Listing all departments and their employees, including departments with no employees.
SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName FROM Employees RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
  • FULL JOIN: Combining both left and right joins to show all employees and departments, including those without matches in either table.
SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName FROM Employees FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
  • CROSS JOIN: Generating all possible combinations of products and categories for a marketing analysis.
SELECT Products.ProductName, Categories.CategoryName FROM Products CROSS JOIN Categories;
  • SELF JOIN: Finding employees and their direct managers within the same employees table.
SELECT a.EmployeeID, a.Name, b.Name AS ManagerName FROM Employees a, Employees b WHERE a.ManagerID = b.EmployeeID;

Summary

  • INNER JOIN: Matches rows in both tables.
  • LEFT JOIN: All rows from the left table, matched rows from the right.
  • RIGHT JOIN: All rows from the right table, matched rows from the left.
  • FULL JOIN: All rows from both tables, with NULLs where there’s no match.
  • CROSS JOIN: Cartesian product of both tables.
  • SELF JOIN: A table joined with itself.

Understanding and effectively using SQL joins allows for sophisticated querying and data analysis, making it possible to retrieve meaningful insights from complex relational databases. 💻

Related Posts