Back to all posts

SQL Joins Explained: INNER, LEFT, RIGHT, and FULL JOIN Diagrams

For someone writing their first queries over two tables; each join type is shown with the rows it returns and the rows it drops.

On this page

A join pairs each row of one table with the matching rows of another table, and returns each pair as one row. For example, you can list each employee with the name of their department.

The join type decides what happens to a row that has no match in the other table. INNER JOIN drops such rows. LEFT JOIN keeps those of the left table, RIGHT JOIN those of the right table, and FULL JOIN those of both.

Every join has the same shape:

SELECT columns
FROM left_table
join_type right_table
  ON left_table.column = right_table.column;
  • left_table is the table named after FROM.
  • join_type is INNER JOIN, LEFT JOIN, RIGHT JOIN or FULL JOIN. It decides which rows without a match stay in the result.
  • right_table is the table named after the join type.
  • ON sets the condition that pairs the rows. It usually compares a foreign key with the primary key that it points to.

Create two sample tables, with three departments and three employees:

CREATE TABLE Departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE Employees (
    employee_id   INT PRIMARY KEY,
    name          VARCHAR(50),
    department_id INT,
    manager_id    INT
);

INSERT INTO Departments VALUES
    (10, 'Engineering'),
    (20, 'Sales'),
    (30, 'HR');

INSERT INTO Employees VALUES
    (1, 'Sarah James', 10,   NULL),
    (2, 'Mark White',  20,   1),
    (3, 'Olivia Reed', NULL, 1);

Two rows have no match: the employee Olivia Reed has no department, and the department HR has no employee. The join types differ only in what they do with these two rows:

The sample rows, with Sarah James paired with Engineering and Mark White with Sales. Olivia Reed and HR have no match. INNER JOIN returns the two pairs, LEFT JOIN adds Olivia Reed, RIGHT JOIN adds HR, and FULL JOIN adds both

The queries below give each table a short alias after its name, such as e for Employees, and write e.name for the name column of Employees. Every query was run on PostgreSQL 18.6, MySQL 9.1, MariaDB 12.3 and SQLite 3.50, and each database that accepts a query returned the same rows.

INNER JOIN

INNER JOIN returns only the pairs of rows that match the ON condition. For example, you can list the employees that have a department, each with the department's name.

SELECT e.name, d.department_name
FROM Employees e
INNER JOIN Departments d
  ON e.department_id = d.department_id;
namedepartment_name
Sarah JamesEngineering
Mark WhiteSales

Sarah James and Mark White each matched one department. Olivia Reed's department_id is NULL, and a comparison with NULL is never true, so she drops out. HR drops out too, because no employee has department 30.

JOIN without INNER is the same join.

Practice this: Lesson 9 of the SQL tutorial runs JOIN over two and three tables of a shop database in your browser, with exercises.

LEFT JOIN

LEFT JOIN returns every row of the left table, with the matching row of the right table beside it. For example, you can list every employee with their department, including the employees that have none.

SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d
  ON e.department_id = d.department_id;
namedepartment_name
Sarah JamesEngineering
Mark WhiteSales
Olivia ReedNULL

Olivia Reed is back, with NULL as her department_name, because no department matched her. HR is still missing, because LEFT JOIN adds back only the unmatched rows of the left table.

Practice this: Lesson 10 of the SQL tutorial runs LEFT JOIN on a shop database in your browser, with exercises.

RIGHT JOIN

RIGHT JOIN returns every row of the right table, with the matching row of the left table beside it. For example, you can list every department with its employees, including the departments that have none.

SELECT e.name, d.department_name
FROM Employees e
RIGHT JOIN Departments d
  ON e.department_id = d.department_id;
namedepartment_name
Sarah JamesEngineering
Mark WhiteSales
NULLHR

HR is now in the result, with NULL as the employee's name. Olivia Reed has dropped out, because RIGHT JOIN adds back only the unmatched rows of the right table.

Write FROM Departments d LEFT JOIN Employees e instead, and you get the same three rows. Prefer the LEFT JOIN. It puts the table whose rows all stay first in the query.

FULL JOIN

FULL JOIN returns the matching pairs, plus the unmatched rows of both tables. For example, you can find the employees without a department and the departments without an employee in one query.

SELECT e.name, d.department_name
FROM Employees e
FULL OUTER JOIN Departments d
  ON e.department_id = d.department_id;
namedepartment_name
Sarah JamesEngineering
Mark WhiteSales
Olivia ReedNULL
NULLHR

Olivia Reed comes from the left table and HR from the right, each with NULL on the side that had no match. FULL JOIN and FULL OUTER JOIN are the same join.

MySQL and MariaDB have no FULL JOIN. The workaround with UNION is under "What goes wrong".

CROSS JOIN

CROSS JOIN pairs every row of the left table with every row of the right table, and takes no ON condition. For example, you can pair each employee with each department to plan a rotation through all of them.

SELECT e.name, d.department_name
FROM Employees e
CROSS JOIN Departments d;

Three of the nine rows, the ones for Sarah James:

namedepartment_name
Sarah JamesEngineering
Sarah JamesSales
Sarah JamesHR

Mark White and Olivia Reed get the same three departments, so the query returns 3 × 3 = 9 rows. CROSS JOIN compares no columns, so Olivia Reed gets the three departments too.

A comma between the tables, FROM Employees e, Departments d, returns the same nine rows. If you meant a join and forgot the condition in WHERE, you get every combination by accident.

SELF JOIN

A self join joins a table to itself, under two aliases. For example, you can list each employee with the name of their manager. In Employees, manager_id holds the employee_id of the employee's manager.

SELECT e.name, m.name AS manager_name
FROM Employees e
LEFT JOIN Employees m
  ON e.manager_id = m.employee_id;
namemanager_name
Sarah JamesNULL
Mark WhiteSarah James
Olivia ReedSarah James

The alias e stands for the employee, and the alias m for the manager, a second row of the same table. Mark White and Olivia Reed have manager 1, who is Sarah James. Sarah James has no manager, and the LEFT JOIN keeps her with NULL.

Joins over more than two tables

A second JOIN adds a third table to the query. For example, you can list each employee with their department and their manager.

SELECT e.name, d.department_name, m.name AS manager_name
FROM Employees e
INNER JOIN Departments d ON e.department_id = d.department_id
INNER JOIN Employees m ON e.manager_id = m.employee_id;
namedepartment_namemanager_name
Mark WhiteSalesSarah James

The first INNER JOIN drops Olivia Reed, who has no department. The second drops Sarah James, who has no manager. Only Mark White matches both conditions.

Write LEFT JOIN for each join, and all three employees stay.

How to choose the right JOIN

If the rows of one table must all stay in the result, write that table after FROM. Then join the other table with LEFT JOIN.

You needJoin
only the rows that match in both tablesINNER JOIN
every row of the left tableLEFT JOIN
every row of the right tableRIGHT JOIN, or LEFT JOIN with the tables swapped
every row of both tablesFULL JOIN
the rows of the left table that have no matchLEFT JOIN with WHERE ... IS NULL
every combination of rowsCROSS JOIN
rows linked to other rows of the same tablea self join

After a LEFT JOIN, a row without a match has NULL in every column of the right table. Test the right table's key for NULL, and you keep only those rows:

SELECT e.name
FROM Employees e
LEFT JOIN Departments d
  ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
name
Olivia Reed

The pattern is called an anti-join.

What goes wrong with joins

A WHERE condition turns LEFT JOIN into INNER JOIN

The goal is every employee, with a department name only for Sales. A WHERE condition on the department name returns one row:

SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d
  ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';
namedepartment_name
Mark WhiteSales

The LEFT JOIN kept every employee, with NULL where no department matched. Then WHERE compared each department_name with 'Sales'. The comparison is never true for NULL, so WHERE removed those rows.

Move the condition into ON. The condition then applies while the rows are paired, and LEFT JOIN still keeps every employee:

SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d
  ON e.department_id = d.department_id
 AND d.department_name = 'Sales';
namedepartment_name
Sarah JamesNULL
Mark WhiteSales
Olivia ReedNULL

Put a condition on the right table in ON, and a condition on the left table in WHERE. The exception is a test for IS NULL, as in the anti-join above.

More rows than expected

A join returns one row for each matching pair. When a row matches several rows of the other table, the join returns that row once per match. This query lists the managers:

SELECT m.name AS manager_name, e.name
FROM Employees m
INNER JOIN Employees e
  ON e.manager_id = m.employee_id;
manager_namename
Sarah JamesMark White
Sarah JamesOlivia Reed

Sarah James manages two employees, so she appears twice. For one row per manager, count the matches with GROUP BY:

SELECT m.name AS manager_name, COUNT(*) AS employees
FROM Employees m
INNER JOIN Employees e
  ON e.manager_id = m.employee_id
GROUP BY m.name;
manager_nameemployees
Sarah James2

FULL JOIN in MySQL and MariaDB

MySQL 9.1 and MariaDB 12.3 reject the FULL OUTER JOIN query with error 1064, "You have an error in your SQL syntax". The join syntax of MySQL 8.4 and of MariaDB has no FULL JOIN.

Combine a LEFT JOIN with the same LEFT JOIN, tables swapped, and let UNION merge them:

SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d
  ON e.department_id = d.department_id

UNION

SELECT e.name, d.department_name
FROM Departments d
LEFT JOIN Employees e
  ON e.department_id = d.department_id;

MySQL 9.1 returns the four rows of FULL JOIN:

namedepartment_name
Sarah JamesEngineering
Mark WhiteSales
Olivia ReedNULL
NULLHR

Each half returns three rows, and the two matched pairs are in both halves. UNION removes duplicate rows, so each pair appears once.

Support for RIGHT JOIN and FULL JOIN differs by database:

DatabaseRIGHT JOINFULL JOIN
PostgreSQL 18yesyes
MySQL 8.4 and 9.1yesno
MariaDB 12.3yesno
SQLite 3.39 and lateryesyes
SQL Server 2022yesyes

On a SQLite older than 3.39, swap the tables for a LEFT JOIN, and use the UNION above for a FULL JOIN.

Build JOIN queries visually in DbSchema

The SQL Editor in DbSchema runs a query and shows its rows in a grid under the editor. Press Editor in the toolbar, choose SQL Editor, paste a query from this article, and press Run.

The LEFT JOIN query in the DbSchema SQL Editor, with its three rows in the result grid: Sarah James with Engineering, Mark White with Sales, and Olivia Reed with NULL

The Query Builder in DbSchema writes the join for you from the diagram. It uses the foreign keys between the tables, so you never type the ON condition.

  1. Right-click a table on the diagram and choose Query Editor, which opens the Query Builder.
  2. Click the icon at the right of a key column, and choose the table to join.
  3. Click the join label on the line between the tables, and choose Left Join or Full Join in place of the inner join. Then press Run.
The DbSchema Query Builder with customers joined to orders by an inner join, the generated SQL at the right and the result rows below

Download DbSchema at https://dbschema.com/download.html and connect it to your database. Run the CREATE TABLE and INSERT statements in the SQL Editor, then each join. The free Community Edition includes the database connection, the diagram and the SQL Editor, and DbSchema Pro adds the Query Builder.

DbSchema ER diagram designer DbSchema ER diagram designer

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download