When working with relational databases, data is often spread across multiple tables. To query it effectively, we use JOINs — a way to combine rows from two or more tables based on a related column.
In this post, we’ll break down the four main types of SQL JOINs — INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN — with simple examples using Customers and Orders.
Sample Data Setup
Let’s create two tables and insert some sample rows:
-- Customers
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100)
);
INSERT INTO Customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Carol'),
(4, 'Dave'); -- Dave has no orders
-- Orders
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_total DECIMAL(10,2)
);
INSERT INTO Orders (order_id, customer_id, order_total) VALUES
(101, 1, 120.00), -- Alice
(102, 1, 80.50), -- Alice
(103, 2, 45.00), -- Bob
(104, NULL, 60.00), -- Unknown customer
(105, 99, 30.00); -- Orphaned order (customer doesn’t exist)
1. INNER JOIN
Definition: Returns only the rows with matching keys in both tables.
Use case: Fetch orders that belong to valid customers.
SELECT c.customer_id, c.customer_name, o.order_id, o.order_total
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id;
Result:
| customer_id | customer_name | order_id | order_total |
|---|---|---|---|
| 1 | Alice | 101 | 120.00 |
| 1 | Alice | 102 | 80.50 |
| 2 | Bob | 103 | 45.00 |
👉 Only Alice and Bob’s orders appear. Dave has no orders, and orphaned/NULL orders are excluded.
2. LEFT JOIN (or LEFT OUTER JOIN)
Definition: Returns all rows from the left table (Customers) and the matched rows from the right table (Orders). Non-matching rows from the right table become NULL.
Use case: Show all customers, including those without orders.
SELECT c.customer_id, c.customer_name, o.order_id, o.order_total
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
Result:
| customer_id | customer_name | order_id | order_total |
|---|---|---|---|
| 1 | Alice | 101 | 120.00 |
| 1 | Alice | 102 | 80.50 |
| 2 | Bob | 103 | 45.00 |
| 3 | Carol | NULL | NULL |
| 4 | Dave | NULL | NULL |
👉 Carol and Dave show up even though they haven’t placed any orders.
3. RIGHT JOIN (or RIGHT OUTER JOIN)
Definition: Returns all rows from the right table (Orders) and the matched rows from the left table (Customers). Non-matching rows from the left table become NULL.
Use case: Show all orders, even if they don’t belong to valid customers.
SELECT c.customer_id, c.customer_name, o.order_id, o.customer_id AS order_customer_id, o.order_total
FROM Customers c
RIGHT JOIN Orders o ON c.customer_id = o.customer_id;
Result:
| customer_id | customer_name | order_id | order_customer_id | order_total |
|---|---|---|---|---|
| 1 | Alice | 101 | 1 | 120.00 |
| 1 | Alice | 102 | 1 | 80.50 |
| 2 | Bob | 103 | 2 | 45.00 |
| NULL | NULL | 104 | NULL | 60.00 |
| NULL | NULL | 105 | 99 | 30.00 |
👉 Orders without valid customers (NULL or 99) are still listed.
4. FULL OUTER JOIN
Definition: Combines the results of both LEFT JOIN and RIGHT JOIN. Returns all rows from both tables, with NULL where there is no match.
Use case: Get a complete picture of customers and orders, whether matched or not.
-- PostgreSQL syntax
SELECT COALESCE(c.customer_id, o.customer_id) AS key_id,
c.customer_name,
o.order_id,
o.order_total
FROM Customers c
FULL OUTER JOIN Orders o ON c.customer_id = o.customer_id;
Result:
| key_id | customer_name | order_id | order_total |
|---|---|---|---|
| 1 | Alice | 101 | 120.00 |
| 1 | Alice | 102 | 80.50 |
| 2 | Bob | 103 | 45.00 |
| 3 | Carol | NULL | NULL |
| 4 | Dave | NULL | NULL |
| NULL | NULL | 104 | 60.00 |
| 99 | NULL | 105 | 30.00 |
👉 Full outer join gives you everything: valid matches, customers without orders, and orders without customers.
Key Takeaways
- INNER JOIN → Only rows with matches in both tables.
- LEFT JOIN → All rows from the left table, matched or not.
- RIGHT JOIN → All rows from the right table, matched or not.
- FULL OUTER JOIN → All rows from both tables, matched or not.
⚡ Pro tip: Be careful with filters in WHERE. For example, WHERE o.order_total > 0 after a LEFT JOIN can unintentionally drop customers without orders, effectively converting it into an INNER JOIN.
Final Thoughts
JOINs are essential to relational databases. Understanding when to use each type helps you write cleaner, more efficient queries that reflect real-world business scenarios — whether you need only matching data (INNER), all customers (LEFT), all orders (RIGHT), or everything combined (FULL).