top of page
Writer's pictureIOTA ACADEMY

Understanding SQL Joins: A Comprehensive Guide

What are Joins in SQL?

In the realm of databases, SQL (Structured Query Language) is the backbone for

managing and manipulating data. One of the most powerful features of SQL is the

ability to combine data from multiple tables using joins. A join is a SQL operation that

combines rows from two or more tables based on a related column between them. This

allows for a more complex and detailed query of data, which is crucial in relational

databases


Importance of Joins

Joins are essential for several reasons:


  1. Data Integration: Joins allow data from different sources to be combined, providing a more comprehensive view of the information.

  2. Efficiency: They reduce the need for multiple queries and subqueries, making data retrieval faster and more efficient.

  3. Flexibility: Joins enable complex queries that can retrieve data based on various relationships between tables.

  4. Normalization: They support database normalization by allowing the division of data into different tables, thus minimizing redundancy and improving data integrity.


Types of Joins

SQL joins are primarily classified into four types, each serving a unique purpose:


  1. Inner Join

  2. Left (Outer) Join

  3. Right (Outer) Join

  4. Full (Outer) Join


Joins in SQL


Real Case Use with an Example

Let's consider a practical example involving two tables: Employees and Departments.


Sample Table

1. Inner Join

An inner join returns only the rows where there is a match in both tables. If there are

rows in either table that do not have matches, they are not included in the result set.


General Syntax:

SELECT *

FROM table1

INNER JOIN table2

ON table1.common_column = table2.common_column;


Example:

SELECT *

FROM Customer

INNER JOIN Orders

ON Customer.CustomerID = Orders.CustomerID;


Here, Inner Join will return only 3 rows, which were matched during join. Customers who has

placed an order and are currently part of the customer table.

Inner Join

2. Left (Outer) Join

A left join (or left outer join) returns all the rows from the left table and the matched

rows from the right table. If there is no match, the result is NULL from the right side.


General Syntax:

SELECT *

FROM table1

LEFT JOIN table2

ON table1.common_column = table2.common_column;

Example:

SELECT *

FROM Customer

LEFT JOIN Orders

ON Customer.CustomerID = Orders.CustomerID;


Here, LEFT Join will return 5 rows, which consist of matched rows of both the table plus

unmatched rows of the left table. It will return all the Customers who exist in the table and also details of their purchase.


Left Join

3. Right (Outer) Join

A right join (or right outer join) is the opposite of the left join. It returns all rows from the

right table and the matched rows from the left table. If there is no match, the result is

NULL from the left side.


General Syntax:

SELECT *

FROM table1

RIGHT JOIN table2

ON table1.common_column = table2.common_column;

Example:

SELECT *

FROM Customer

RIGHT JOIN Orders

ON Customer.CustomerID = Orders.CustomerID;


Here, RIGHT Join will return 4 rows, which consist of matched rows of both the table plus

unmatched rows of the right table. It will return details about all the orders and also about

customers who had placed those orders.


Right Join

4. Full (Outer) Join

A full join (or full outer join) returns all rows when there is a match in one of the tables. If

there is no match, the result is NULL from the side without a match.


General Syntax:

SELECT *

FROM table1

FULL JOIN table2

ON table1.common_column = table2.common_column;

Example:

SELECT *

FROM Customer

FULL JOIN Orders

ON Customer.CustomerID = Orders.CustomerID;


Here, FULL Join will return 6 rows, which consist of matched rows of both the table plus

unmatched rows of both right table and left table. It will return details about all the orders and all the customers and their relations as well if it exists.


Full Outer Join

Note: In case of MySQL, we have to use the UNION ALL operator to combine the

result set of LEFT and RIGHT JOIN as the FULL JOIN construct does not work there.


Differences Between Joins


  • Inner Join: Only returns rows with matching values in both tables.

  • Left Join: Returns all rows from the left table and matched rows from the right table; unmatched rows from the right table are filled with NULL.

  • Right Join: Returns all rows from the right table and matched rows from the left table; unmatched rows from the left table are filled with NULL.

  • Full Join: Returns rows when there is a match in one of the tables. Unmatched rows from both tables are filled with NULL.


Additional Topics


Self Join

A self join is a join in which a table is joined with itself. This is useful for hierarchical

data or comparing rows within the same table.


General Syntax:

SELECT A.EmployeeID, A.Name, B.Name AS ManagerName

FROM Employees A

INNER JOIN Employees B

ON A.ManagerID = B.EmployeeID;


Cross Join

A cross join returns the Cartesian product of the two tables, meaning it combines all

rows from the first table with all rows from the second table.


General Syntax:

SELECT Employees.Name, Departments.DepartmentName

FROM Employees

CROSS JOIN Departments;


Conclusion

SQL joins are a fundamental part of database management, enabling the integration of

data across multiple tables. Understanding the different types of joins and their use

cases is essential for efficient database querying and manipulation. Whether it's

combining employee and department information or handling more complex data

relationships, mastering SQL joins can significantly enhance your ability to work with

relational databases.

Comments


bottom of page