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

Introduction

SQL joints are a fundamental characteristic to combine data from several tables based on a related column. It is essential to understand the different types of joints and their applications to work effectively with relational databases. In this article, we will explore various types of SQL joints with clear explanations and practical examples.

Table samples

We will use the following customers and orders for examples:

Table of customers:

Control table:

1. Inner join: Combination of corresponding data

Inner Join recovers the lines that have corresponding values ​​in the two tables. It is the most used type of joint.

Example: Combining customers and their orders

Request::

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.
• Diana clientele and 105 order are excluded because they have no corresponding files in the two tables.

2. Left join: including all records in the left table

The left on the left returns all the lines of the left table (customers), as well as the corresponding lines of the right table (controls). The lines without correspondence in the right table will have zero values.

Example: including orders without orders

Request::

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 have no orders.
• Diana appears with Null for the product and the quantity.

3. Right Rewing: including all recordings in the right table

The right -right joint is the opposite of the left joint. It includes all the lines of the right table (controls) and corresponding lines of the left table (customers). The lines without correspondence in the left table will have zero values.

Example: including client orders

Request::

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 have no matching customers.
• The 105 command appears with null for the name because Customerid = 5 is not in the customer table.

4. Complete exterior completion: including all recordings of the two tables

Full OUTER Join combines the results of the left joint and the right joint, returning all the lines of the two tables. The lines without correspondence will have zero values ​​for the missing columns.

Example: combining all customers and orders

Request:

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 the 105 order (no corresponding customer) appear with zero values.

5. Cross Join: Cartesian product

Cross Join returns the Cartesian product of two tables, pressing each line of the left table with each line of the right table.

Example: Association of customers with products

Request::

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

Result::

Explanation::

• Each customer is associated with each product, which results from 20 lines (4 customers x 5 orders).

6. Self-attachment: join a table with itself

Self Join is used to compare the lines in the same table. It is useful for hierarchical or relational data.

Example: Employee-management relationship

Suppose we have a table of employees:

Request: 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 attached to itself using Managerid and Employeid to associate employees with their managers.

Summary of joints

Conclusion

Understanding SQL joints is the key to working with relational databases. Each type of joint serves a single objective, and mastering them will help you combine and analyze data effectively. Practice these examples to solidify your understanding!

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