Mastering Inner vs. Outer Joins
In this lesson, you’ll learn how to control which records appear in your result set when merging data from two different tables.
When you join two tables, you are mapping data across a "relational boundary"—the connection point between your records. The type of join you choose acts as a filter that determines if a row must have a match on both sides to be included or if it can survive alone.
The Inner Join: The "Match-Only" Filter
An INNER JOIN is the strict gatekeeper. It only returns rows where there is a valid match in both tables. If a record in your first table doesn't have a corresponding partner in the second table, that record is excluded from your results entirely. Think of it as a social dance where you only allow pairs on the floor; if someone doesn't have a partner, they simply aren't included in the output.
SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;
In this example, if an order exists but is missing a valid customer link, that order will not show up in your results.
The Outer Join: The "Inclusive" Filter
An LEFT OUTER JOIN (often just called a LEFT JOIN) is much more permissive. It keeps all records from the table on the left, even if they don't have a match in the table on the right. When there is no match, the database simply fills in the missing information with NULL.
SELECT orders.id, customers.name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.id;
Here, if an order lacks a matching customer, the query still returns the order record, but shows NULL for the customer’s name.
When to use which?
- Use an Inner Join when you require complete data integrity and only care about paired information.
- Use an Outer Join when you need a comprehensive list of items from one source, regardless of whether they have related secondary data.
Key Takeaway: Inner Joins act as a filter that discards non-matching rows, while Outer Joins preserve your primary dataset by allowing records to remain even when no match exists in the secondary table.