SQL Joins Explained

Good databases split information across several tables instead of repeating it everywhere. Client details sit in one table, and projects sit in another. A join is how you bring them back together in a single result by matching a shared column such as client_id.

The join type decides what happens to rows that find no match on the other side, and that is the part beginners usually find confusing.

The Two Tables

Here are clients and projects for a small design agency. Notice two things. Meadow Fitness has no projects yet, and the Brand Audit project points to client 9, which doesn't exist in the clients table.

clients

projects

INNER JOIN

INNER JOIN keeps only the rows that have a match on both sides. Anything without a partner is dropped.

SELECT c.client_name, p.project_name, p.budget
FROM clients AS c
INNER JOIN projects AS p
    ON c.client_id = p.client_id;

Output:

Code Explanation:

• c and p are short aliases, which save typing and make it clear which table each column comes from.
• ON c.client_id = p.client_id is the matching rule. A client row and a project row are paired only when these two values are equal.
• Orion Labs appears twice because it has two projects, and each match becomes its own row.
• Meadow Fitness and the Brand Audit project are missing because neither found a partner.

LEFT JOIN

LEFT JOIN keeps every row from the first (left) table, even when nothing matches. The missing side is filled with NULL.

SELECT c.client_name, p.project_name
FROM clients AS c
LEFT JOIN projects AS p
    ON c.client_id = p.client_id;

Output:

Code Explanation:

• We keep every client because clients is on the left side of the join.
• Meadow Fitness has no project, so project_name comes back as NULL.
• The orphan Brand Audit project is still missing, because it belongs to the right table and has no left-side match.

A handy trick follows from this. Add WHERE p.project_id IS NULL to the query above, and you get only the clients with no projects at all. This is a common way to find data gaps.

How Each SQL Join Type Handles Unmatched Rows

MySQL does not support FULL JOIN. The usual workaround is to combine a LEFT JOIN and a RIGHT JOIN with UNION.