How to Master SQL Joins – Inner, Outer, Cross, and Self-Joins With Examples | HackerNoon

Introdution

SQL joins are a fundamental feature for combining data from multiple tables based on a related column. Understanding the different types of joins and their applications is essential for working with relational databases effectively. In this article, we’ll explore various SQL join types with clear explanations and practical examples.

Sample Tables

We’ll use the following Customers and Orders tables for examples:

Customers Table:

Orders Table:

1. INNER JOIN: Combining Matching Data

INNER JOIN retrieves rows that have matching values in both tables. It is the most commonly used join type.

Example: Combining Customers and Their Orders

Query:

SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

Explanation:

• Only customers with orders appear in the result.
• Customer Diana and order 105 are excluded because they don’t have matching records in both tables.

2. LEFT JOIN: Including All Records From the Left Table

LEFT JOIN returns all rows from the left table (Customers), along with matching rows from the right table (Orders). Rows with no match in the right table will have NULL values.

Example: Including Customers Without Orders

Query:

SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

Explanation:

• All customers are included, even if they don’t have orders.
• Diana appears with NULL for Product and Quantity.

3. RIGHT JOIN: Including All Records From the Right Table

RIGHT JOIN is the opposite of LEFT JOIN. It includes all rows from the right table (Orders) and matching rows from the left table (Customers). Rows with no match in the left table will have NULL values.

Example: Including Orders Without Customers

Query:

SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

Explanation:

• All orders are included, even if they don’t have matching customers.
• Order 105 appears with NULL for Name because CustomerID = 5 is not in the Customers table.

4. FULL OUTER JOIN: Including All Records from Both Tables

FULL OUTER JOIN combines the results of LEFT JOIN and RIGHT JOIN, returning all rows from both tables. Rows with no match will have NULL values for the missing columns.

Example: Combining All Customers and Orders

Query:

SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

Explanation:

• All customers and orders are included.
• Diana (no orders) and order 105 (no matching customer) appear with NULL values.

5. CROSS JOIN: Cartesian Product

CROSS JOIN returns the Cartesian product of two tables, pairing every row from the left table with every row from the right table.

Example: Pairing Customers with Products

Query:

SELECT Customers.Name, Orders.Product
FROM Customers
CROSS JOIN Orders;

Result:

Explanation:

• Every customer is paired with every product, resulting in 20 rows (4 Customers x 5 Orders).

6. SELF JOIN: Joining a Table with Itself

SELF JOIN is used to compare rows within the same table. It is useful for hierarchical or relationship data.

Example: Employee-Manager Relationship

Assume we have an Employees table:

Query: Find employees and their managers.

SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
LEFT JOIN Employees E2
ON E1.ManagerID = E2.EmployeeID;

Result:

Explanation:

• The table is joined with itself using ManagerID and EmployeeID to associate employees with their managers.

Summary of Joins

Conclusion

Understanding SQL joins is key to working with relational databases. Each join type serves a unique purpose, and mastering them will help you combine and analyze data efficiently. Practice these examples to solidify your understanding!

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn. Happy exploring!👋