SQL Joins Explained with Examples

1. INNER JOIN (or JOIN)

The INNER JOIN returns rows only when there is a match in both tables based on the specified join condition.

SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Retrieving customers and their corresponding orders.

Result:

Notice that “New Customer” (CustomerID 6) is not included because there are no matching orders, and the order with OrderID 10253 is not included because its CustomerID is NULL and doesn’t match any CustomerID in the Customers table.

2. LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN returns all rows from the left table (Customers in this case), and the matching rows from the right table (Orders). If there is no match in the right table, NULL values are used for the columns from the right table.

SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Retrieving all customers and their orders (if any).

Result:

All customers are listed. Customers with no orders have NULL values for OrderID and OrderDate.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN returns all rows from the right table (Orders), and the matching rows from the left table (Customers). If there is no match in the left table, NULL values are used for the columns from the left table.

SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Retrieving all orders and their corresponding customers (if any).

Result:

All orders are listed. The order with CustomerID as NULL has NULL for CustomerName.

4. FULL OUTER JOIN (or FULL JOIN)

The FULL OUTER JOIN returns all rows when there is a match in either the left table (Customers) or the right table (Orders). If there is no match in one of the tables, NULL values are used for the columns from the table without a match. (Note: Some older SQL databases may not fully support FULL OUTER JOIN).

SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Retrieving all customers and all orders, with NULLs where there’s no match.

Result:

This result includes all customers and all orders. Where a customer has no orders, the order columns are NULL. Where an order has no matching customer, the customer columns are NULL.

5. CROSS JOIN

The CROSS JOIN returns the Cartesian product of the rows from the joined tables. This means it combines each row from the first table with each row from the second table. It typically doesn’t have an ON clause.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;

Creating all possible combinations of customers and orders.

Result (Partial):

This will produce a result set with 6 (customers) * 6 (orders) = 36 rows.

6. Self JOIN

A SELF JOIN is used to join a table to itself. This is useful when the table has a hierarchical relationship or when you need to compare rows within the same table.

Let’s imagine an Employees table with EmployeeID and ManagerID columns.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(255),
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) VALUES
(1, 'John Smith', NULL),
(2, 'Jane Doe', 1),
(3, 'Peter Jones', 1),
(4, 'Mary Brown', 2),
(5, 'David Wilson', 3);

SELECT e1.EmployeeName AS Employee, e2.EmployeeName AS Manager
FROM Employees e1
INNER JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

Finding each employee and their manager.

Result: