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:
Customerid |
Name |
Country |
---|---|---|
1 |
Alice |
USA |
2 |
Bob |
Canada |
3 |
Charlie |
UNITED KINGDOM |
4 |
Diane |
Germany |
Control table:
Prescription |
Customerid |
Product |
Quantity |
---|---|---|---|
101 |
1 |
Laptop |
2 |
102 |
1 |
Mouse |
5 |
103 |
2 |
Keyboard |
3 |
104 |
3 |
Monitor |
1 |
105 |
5 |
Smartphone |
2 |
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::
Name |
Product |
Quantity |
---|---|---|
Alice |
Laptop |
2 |
Alice |
Mouse |
5 |
Bob |
Keyboard |
3 |
Charlie |
Monitor |
1 |
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::
Name |
Product |
Quantity |
---|---|---|
Alice |
Laptop |
2 |
Alice |
Mouse |
5 |
Bob |
Keyboard |
3 |
Charlie |
Monitor |
1 |
Diane |
NULL |
NULL |
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::
Name |
Product |
Quantity |
---|---|---|
Alice |
Laptop |
2 |
Alice |
Mouse |
5 |
Bob |
Keyboard |
3 |
Charlie |
Monitor |
1 |
NULL |
Smartphone |
2 |
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::
Name |
Product |
Quantity |
---|---|---|
Alice |
Laptop |
2 |
Alice |
Mouse |
5 |
Bob |
Keyboard |
3 |
Charlie |
Monitor |
1 |
Diane |
NULL |
NULL |
NULL |
Smartphone |
2 |
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::
Name |
Product |
---|---|
Alice |
Laptop |
Alice |
Mouse |
Alice |
Keyboard |
Alice |
Monitor |
Alice |
Smartphone |
Bob |
Laptop |
Bob |
Mouse |
… |
… |
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:
Employee |
Name |
Manager |
---|---|---|
1 |
Alice |
3 |
2 |
Bob |
3 |
3 |
Charlie |
NULL |
4 |
Diane |
1 |
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::
Employee |
Director |
---|---|
Alice |
Charlie |
Bob |
Charlie |
Charlie |
NULL |
Diane |
Alice |
Explanation::
- The table is attached to itself using Managerid and Employeid to associate employees with their managers.
Summary of joints
Membership type |
Description |
Example of use cases |
---|---|---|
Interior joint |
Corresponds to the lines in the two tables. |
Customers with orders. |
Join |
All the lines of the left table, corresponding to the lines of the right. |
Customers with or without orders. |
Right |
All the lines of the right table, corresponding to the lines of the left. |
Orders with or without customers. |
Complete exterior joint |
All the lines of the two tables, with nulls for the missing games. |
Complete customer and order data. |
Join |
Cartesian product of two tables. |
Twin customers with products. |
Join you |
Join a table with itself. |
Relations with employees-management. |
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! 👋