Different types of SQL joins

Structured query language (SQL) is a language that is used in relational databases to store, retrieve, and manipulate data.

SQL provides a join functionality that allows us to combine data from multiple tables based on common columns between the tables.

Types of joins

Below we can see the six different types of joins provided by SQL:

  1. Inner Join

  2. Left Join

  3. Right Join

  4. Full Outer Join

  5. Cross Join

  6. Self Join

Below is a brief explanation of all these different types of SQL joins.

1. Inner join

The inner join query is used when we want to retrieve only those rows from both tables that have matching values in specified columns.

2. Left join

The left join query returns all rows from the left table and the matching rows from the right table. If a match for the left table does not exist in the right table, it will result in NULL values for the columns on the right side.

3. Right join

Similar to the left join, in the right join, we will get matching rows from the left table and all rows from the right table.

4. Full outer join

The full outer join will return all rows from both tables; if there is no match found, the result will contain NULL values in those table columns that do not have a matching value.

5. Cross join

The cross join combines each row of the first table from each row of the second, similar to the cartesian product of the rows in the two tables.

6. Self join

A self join is a specific type of join that is used when a table is joined with itself. It combines rows from the same table based on a related column.

Joins example

Now we will look at the SQL script attached below that shows how these joins work on two tables, Employees and Managers. For the self join, we use a separate table named Manager.

Employees and managers table contents
Employees and managers table contents

We can see the contents of the Employees and Managers table above. Using the tables, we see that Sarah manages John and Michael manages Alice.

Manager table contents
Manager table contents

In the table above, we also see a Manager table that will be used to perform a self join query. This table shows that Pudge manages Sven and Crystal, but no one manages Pudge.

-- Create the Employees table if it doesn't exist
CREATE TABLE IF NOT EXISTS Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50)
);
-- Insert data into the Employees table
INSERT INTO Employees (EmployeeID, EmployeeName) VALUES
(1, 'John'),
(2, 'Alice'),
(3, 'Bob'),
(4, 'Mary'),
(5, 'Jake');
-- Create the Managers table
CREATE TABLE Managers (
    EmployeeID INT PRIMARY KEY,
    ManagerName VARCHAR(50)
);
-- Insert data into the Managers table
INSERT INTO Managers (EmployeeID, ManagerName) VALUES
(1, 'Sarah'),
(2, 'Michael'),
(100, 'Jeff');
-- Display Employees Table
SELECT "Employees Table ->  ", Employees.EmployeeID AS EID, "", Employees.EmployeeName AS EName, ""
FROM Employees;
SELECT "";
-- Display Managers Table
SELECT "Managers Table ->  ", Managers.EmployeeID AS EID, "", Managers.ManagerName AS MName, ""
FROM Managers;
SELECT "";
-- Perform Inner Join
SELECT "Inner Join ->  ", Employees.EmployeeID AS EID, "", Employees.EmployeeName AS EName, "", Managers.ManagerName AS MName
FROM Employees
INNER JOIN Managers
ON Employees.EmployeeID = Managers.EmployeeID;
SELECT "";
-- Perform Left Join
SELECT "Left Join ->  ", Employees.EmployeeID AS EID, "",Employees.EmployeeName AS EName, "",Managers.ManagerName AS MName
FROM Employees
LEFT JOIN Managers
ON Employees.EmployeeID = Managers.EmployeeID;
SELECT "";
-- Perform Right Join
SELECT "Right Join ->  ", Employees.EmployeeID AS EID, Employees.EmployeeName AS EName, Managers.ManagerName AS MName
FROM Employees
RIGHT JOIN Managers
ON Employees.EmployeeID = Managers.EmployeeID;
SELECT "";
-- Perform Full Join
SELECT "Full Outer Join ->  ", Employees.EmployeeID AS EID, Employees.EmployeeName AS EName, Managers.ManagerName AS MName
FROM Employees
LEFT JOIN Managers ON Employees.EmployeeID = Managers.EmployeeID
UNION
SELECT "Full Outer Join ->  ", Employees.EmployeeID AS EID, Employees.EmployeeName AS EName, Managers.ManagerName AS MName
FROM Employees
RIGHT JOIN Managers ON Employees.EmployeeID = Managers.EmployeeID;
SELECT "";
-- Perform Cross Join
SELECT "Cross Join ->  ", Employees.EmployeeID AS EID, Employees.EmployeeName AS EName, Managers.EmployeeID AS MID, Managers.ManagerName AS MName
FROM Employees
CROSS JOIN Managers;
SELECT "";
-- Create the Manager table
CREATE TABLE Manager (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    ManagerID INT
);
-- Insert data into the Manager table
INSERT INTO Manager (EmployeeID, EmployeeName, ManagerID) VALUES
(10, 'Pudge', NULL),
(20, 'Sven', 10),
(30, 'Crystal', 10);
-- Display Manager Table
SELECT "Manager Table ->  ", Manager.EmployeeID AS EID, "", Manager.EmployeeName AS EName, "", Manager.ManagerID AS MID, ""
FROM Manager;
SELECT "";
-- Perform Self Join
SELECT "Self Join ->  ",s1.EmployeeName AS EName, s2.EmployeeName AS MName
FROM Manager s1, Manager s2  
WHERE s1.ManagerID = s2.EmployeeID;
SELECT "";
SQL script to show different types of joins

Code explanation

  • Lines 2–13: We create an employees table and insert five rows into it.

  • Lines 16–25: We create a managers table and insert three rows into it.

  • Lines 28–35: Display both the managers and employees tables.

  • Lines 38–42: Perform an inner join on the tables based on EmployeeID.

  • Lines 45–49: Perform a left join on the column EmployeeID where the left table is Employees, and the right table is Managers.

  • Lines 52–56: Similar to the left join, perform a right join on the column EmployeeID keeping the table orientation the same.

  • Lines 59–66: Perform a full outer join on both tables. Since MySQL does not support the clause, we use a UNION clause to get the same results.

  • Lines 69–72: We perform a cross-join on both tables where the Employees table performs a cartesian product with the Managers table.

  • Lines 75–85: We create a Manager table for which we will perform self-join and insert three rows into the table.

  • Lines 93–96: A self join is performed on the Manager table using the ManagerID and EmployeeID columns.

Unlock your potential: SQL joins series, all in one place!

To continue your exploration of SQL joins, check out our series of Answers below:

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved