First off, let's clear up a common confusion: LEFT JOIN is a type of OUTER JOIN, not a separate, competing operation. Outer joins are a family of joins that preserve rows from one or both tables even when there's no matching row in the other table. Let's break down the categories, then dive into examples to make this tangible.

What is an Outer Join? #

Outer joins come in three core flavors:

• LEFT OUTER JOIN (often shortened to LEFT JOIN): Preserves all rows from the left table, matching rows from the right table where possible.
• RIGHT OUTER JOIN: Preserves all rows from the right table, matching rows from the left table where possible.
• FULL OUTER JOIN: Preserves all rows from both tables, matching where possible and filling in NULLs for non-matching rows on either side.

When someone says "Outer Join" without specifying, they usually mean FULL OUTER JOIN—so let's focus on comparing LEFT JOIN (a subset of outer joins) to FULL OUTER JOIN since that's the most common point of confusion.

Example Tables to Illustrate #

Let's use two simple, relatable tables for our examples:

users table #

orders table #

1. LEFT JOIN (LEFT OUTER JOIN) #

A LEFT JOIN returns every row from the left table (users here), and matches rows from the right table (orders) where the join condition (u.id = o.user_id) holds. If there's no match, columns from the right table are filled with NULL.

SQL Query #

SELECT u.id AS user_id, u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Result #

Notice Bob's row is included even though he has no orders, but the order details are NULL. The order for user 5 (from the orders table) is not included—we only preserve rows from the left table.

2. FULL OUTER JOIN #

A FULL OUTER JOIN returns every row from both tables, matching rows where possible. If a row exists in one table but not the other, columns from the non-matching table are filled with NULL.

SQL Query #

SELECT u.id AS user_id, u.name, o.order_id, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

Result #

Here, we get Bob's row (from users with no orders) and the order for user 5 (from orders with no associated user). Both are included, with NULLs filling in the missing columns.

Key Differences Summary #

• Hierarchy: LEFT JOIN is a specific type of OUTER JOIN. OUTER JOIN is the umbrella term that includes LEFT, RIGHT, and FULL variants.
• Row Preservation:
  • LEFT JOIN preserves all rows from the left table, ignoring unmatched rows from the right table.
  • FULL OUTER JOIN preserves all rows from both tables, including unmatched rows from either side.
• Use Cases:
  • Use LEFT JOIN when you need all records from the primary table (e.g., all users, even those with no orders) plus any matching related data.
  • Use FULL OUTER JOIN when you need a complete picture of both datasets (e.g., all users and all orders, including users with no orders and orders with no associated user).

内容的提问来源于stack exchange，提问作者Mithilesh