The Ultimate Guide to Understanding SQL Joins

What is a join?

In a database, information is often spread across several tables.

A join (JOIN) allows you to link these tables using a common column (often a primary key and a foreign key).

Example: a users table and an orders table can be linked via users.id = orders.user_id.

There are several types of joins, which allow us to link this data, giving it a particular meaning, filtering it, etc. Some joins are native to the DBMS (here MySQL for the examples), while others require some SQL logic to create!

The main joins

INNER JOIN

An inner join (INNER JOIN) only returns the rows from A that have a link with B.

SELECT *
FROM A
INNER JOIN B ON A.key = B.key;

Example: In an e-commerce software, we can use an INNER JOIN between users and orders to display only the customers who have placed an order.

LEFT JOIN

A left join (LEFT JOIN or LEFT OUTER JOIN) returns all rows from A, and the data from B when there is a match (otherwise NULL).

SELECT *
FROM A
LEFT JOIN B ON A.key = B.key;

Example: in a project management software, a LEFT JOIN between projects and tasks allows you to display all projects, even those that don't yet have any assigned tasks.

RIGHT JOIN

A right join (RIGHT JOIN or RIGHT OUTER JOIN) returns all rows from B, and the data from A when there is a match (otherwise NULL).

SELECT *
FROM A
RIGHT JOIN B ON A.key = B.key;

Example: in a booking tool, a RIGHT JOIN between users and bookings allows you to display all bookings, even those linked to deleted accounts.

FULL JOIN

A full outer join (FULL JOIN or FULL OUTER JOIN) returns all rows from A and all rows from B, whether they match or not.

SELECT *
FROM A
LEFT JOIN B ON A.key = B.key
UNION
SELECT *
FROM A
RIGHT JOIN B ON A.key = B.key;

Example: in a school management software, a FULL OUTER JOIN between students and exam_results would allow you to display all students (even those without a grade) and all exams (even those with no participants).

Special joins

Most use cases will be covered by the joins presented previously, but you may come across specific cases that require more complex joins.

LEFT ANTI JOIN

A left anti-join (LEFT ANTI JOIN) returns all rows from A, except when they have a link to B.

SELECT *
FROM A
LEFT JOIN B ON A.key = B.key
WHERE B.key IS NULL;

Example: in a CRM, find customers who have never placed an order.

RIGHT ANTI JOIN

A right anti-join (RIGHT ANTI JOIN) returns all rows from B, except when they have a link to A.

SELECT *
FROM A
RIGHT JOIN B ON A.key = B.key
WHERE A.key IS NULL;

Example: in an HR software, display employees registered in payroll but not yet present in the main HR database.

FULL ANTI JOIN

A full anti-join (FULL ANTI JOIN) returns all rows from A and B, except those that create a link between A and B.

SELECT *
FROM A
FULL OUTER JOIN B ON A.key = B.key
WHERE A.key IS NULL OR B.key IS NULL;

Example: on an animal shelter website, we want to find all the animals that no longer have owners, and all the owners who no longer have animals.