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

Relational databases store data across multiple tables to avoid duplication. Customer details live in one table, orders in another. But eventually, you need to combine them. That's what JOINs do.

Consider two tables. A customers table with customer names, and an orders table with what each customer bought. They're connected by a customer_id foreign key. When you query, you tell the database how to match rows between them.

The four fundamental join types are INNER, LEFT, RIGHT, and FULL OUTER. Each one handles non-matching rows differently.

Key sources: PostgreSQL documentation on table joins, "SQL for Dummies" by Allen G. Taylor, and standard SQL-92 join syntax.

The Setup

For all examples, assume these two tables:

customers

| id | name | |----|------| | 1 | Alice | | 2 | Bob | | 3 | Charlie |

orders

| id | customer_id | item | |----|------------|------| | 101 | 1 | Laptop | | 102 | 1 | Mouse | | 103 | 2 | Keyboard |

Notice that Charlie (id=3) has no orders. This is the edge case that makes each join type behave differently.

INNER JOIN

An INNER JOIN returns only rows where a match exists in both tables. If a customer has no orders, they don't appear. If an order has no customer (orphan record), it also doesn't appear.

sql SELECT c.name, o.item FROM customers c INNER JOIN orders o ON c.id = o.customer_id;

Result:

| name | item | |------|------| | Alice | Laptop | | Alice | Mouse | | Bob | Keyboard |

Charlie is excluded because there's no matching row in orders. This is the most common join type in production systems because it eliminates ambiguity.

Use when: You only care about records that have relationships. A report of all orders with their customer names. An invoice system where every invoice must have a customer.

LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN returns all rows from the left table, plus matching rows from the right table. If no match exists, the right-side columns are filled with NULL.

sql SELECT c.name, o.item FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;

Result:

| name | item | |------|------| | Alice | Laptop | | Alice | Mouse | | Bob | Keyboard | | Charlie | NULL |

Charlie appears now, but with NULL in the item column. This tells you: "Charlie is a customer who hasn't ordered anything yet."

Use when: You need all rows from the primary table, regardless of whether related data exists. A customer list showing their last purchase date (NULL if they've never purchased). A content management system showing all articles even if some have no comments.

RIGHT JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table, plus matching rows from the left. If no match exists, the left-side columns are NULL.

sql SELECT c.name, o.item FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id;

Result:

| name | item | |------|------| | Alice | Laptop | | Alice | Mouse | | Bob | Keyboard |

In this specific example, the result looks identical to INNER JOIN because every order in our orders table has a valid customer_id. RIGHT JOIN becomes useful when you want to preserve all rows from the right table.

Use when: You're auditing or analyzing from the perspective of the related table. In practice, most developers prefer LEFT JOIN and simply swap the table order. RIGHT JOIN is rarely used in production code because LEFT JOIN is more intuitive.

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables. Where matches exist, they're combined. Where they don't, the missing side is NULL.

sql SELECT c.name, o.item FROM customers c FULL OUTER JOIN orders o ON c.id = o.customer_id;

Result:

| name | item | |------|------| | Alice | Laptop | | Alice | Mouse | | Bob | Keyboard | | Charlie | NULL |

If we had an orphan order (an order with a customer_id that doesn't exist in customers), that would also appear with NULL in the name column.

Use when: You need a complete picture of both tables. Data reconciliation — comparing two systems to find records that exist in one but not the other. Merging datasets where you can't afford to lose any data from either side.

Summary Table

| Join Type | Left Table Rows | Right Table Rows | |-----------|:---------------:|:----------------:| | INNER JOIN | Only matched | Only matched | | LEFT JOIN | All | Only matched (NULL otherwise) | | RIGHT JOIN | Only matched (NULL otherwise) | All | | FULL OUTER JOIN | All | All |

Key Takeaways

1. INNER JOIN is the default. Use it when both sides must exist.
2. LEFT JOIN preserves the left table. Use it when the left side is your primary entity.
3. RIGHT JOIN is LEFT JOIN in reverse. Rarely needed in practice.
4. FULL OUTER JOIN keeps everything. Use for reconciliation or audits.
5. Performance note: JOINs with proper indexes on the foreign key columns execute in milliseconds even on tables with millions of rows. A missing index turns JOINs into sequential scans.

These four join types cover the vast majority of relational query patterns. Master them, and you can express almost any data relationship in SQL.