SQL JOINs Explained: INNER, LEFT, RIGHT & FULL

Real data lives in more than one table. A JOIN combines rows from two tables based on a related column between them — for example, matching each Employees.dept_id to a Departments.dept_id. We'll use these two small tables throughout:

Notice: Priya's department (30) doesn't exist in Departments, Wei's department (99) doesn't exist either, and Support (40) has no employees yet. These mismatches are exactly what make the different join types behave differently.

INNER JOIN — only the matches

INNER JOIN returns rows only when the join condition matches on both sides. Anything that doesn't match is dropped.

SELECT e.first_name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON d.dept_id = e.dept_id;

Result: Ananya/Engineering and Tomas/Sales. Priya and Wei are dropped (no matching department), and Support doesn't appear (no matching employee).

LEFT JOIN — everything from the left table

LEFT JOIN keeps every row from the "left" table (the one named right after FROM), and fills in NULL for the right-hand columns when there's no match.

SELECT e.first_name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON d.dept_id = e.dept_id;

Result: all four employees appear. Priya and Wei get NULL for dept_name.

This is the join to reach for when you're asking "show me every X, and whatever related Y exists (if any)" — for example, every product alongside its total orders, including products that have never been ordered.

RIGHT JOIN — everything from the right table

The mirror image of LEFT JOIN: every row from the right-hand table is kept, with NULLs on the left where there's no match.

SELECT e.first_name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON d.dept_id = e.dept_id;

Result: Ananya/Engineering, Tomas/Sales, and NULL/Support — Support has no employees, but the department still appears.

In practice, most people just swap the table order and use LEFT JOIN instead — the results are identical, and it reads more naturally for most queries.

FULL OUTER JOIN — everything, matched where possible

FULL JOIN keeps every row from both tables, matching where it can and filling NULLs on whichever side has no counterpart.

SELECT e.first_name, d.dept_name
FROM Employees e
FULL OUTER JOIN Departments d ON d.dept_id = e.dept_id;

Result: all four employees and all three departments, including Priya/NULL, Wei/NULL, and NULL/Support.

Quick reference

A three-table join

Joins chain naturally — add as many as you need:

SELECT c.company, o.order_id, p.name AS product
FROM Orders o
JOIN Customers c ON c.customer_id = o.customer_id
JOIN Products p ON p.product_id = o.product_id;

Related reading

• SQL Subqueries Explained: Nested SELECT Statements
• GROUP BY vs HAVING in SQL: What's the Difference?