How a Left Outer Join Combines Data From Two Database Tables

A left outer join returns all rows from the first table, plus matching rows from the second

A left outer join is a way to combine data from two database tables. It keeps every row from the table on the left side of the join, and adds columns from the table on the right side whenever there is a match. If a row in the left table has no matching row in the right table, the columns from the right table show as empty.

Think of it like a mailing list joined with a purchase history. The left table is your complete mailing list. The right table is customer purchases. A left outer join gives you every person on the mailing list, with their purchase information filled in where it exists — and blank spaces for people who never bought anything. Nobody gets dropped from the mailing list just because they did not buy.

Key Takeaways

A left outer join keeps all rows from the left table and adds matching data from the right table, leaving blanks where no match exists.
The left table is the one you want to keep complete; the right table is optional data you are adding to it.
Left outer joins are common when you need a full roster with optional details, like employees with their department assignments or customers with their orders.
The syntax in most databases is SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.id, though many databases let you drop the word OUTER.

How a left outer join differs from an inner join

An inner join returns only rows where both tables have a match. If you inner join a mailing list with a purchase history, you get only the people who bought something. Everyone else disappears from the result.

A left outer join keeps the entire left table and adds the right table's data where it matches. If someone on the mailing list never bought anything, they still appear in the result — just with empty cells in the purchase columns. This matters when you need a complete picture of the left table, not just the overlapping part.

When to use a left outer join

Use a left outer join when the left table is your primary dataset and you want to enrich it with optional information from another table. Common examples include:

A list of all employees joined with their department assignments — you want every employee even if their department record is missing.
All customers joined with their most recent order — you want the full customer list even if some customers have never ordered.
All products joined with their inventory counts — you want every product even if it is not currently in stock anywhere.
All users joined with their account settings — you want every user even if they have not customized their settings yet.

The pattern is always the same: the left table is complete and authoritative, and the right table provides optional details.

The syntax and how to read it

The basic structure in most databases (SQL Server, PostgreSQL, MySQL, SQLite) is:

SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.id

Breaking this down: SELECT * means get all columns. FROM table_a is your left table — the one you want to keep complete. LEFT OUTER JOIN table_b adds the right table. ON table_a.id = table_b.id is the condition that decides which rows match. The database pairs rows where the id values are the same.

Many databases let you write just LEFT JOIN instead of LEFT OUTER JOIN — they mean the same thing. The word OUTER is optional and rarely used in practice.

What happens to unmatched rows

When a row in the left table has no matching row in the right table, the columns from the right table fill with NULL — a database term for empty or missing. This is how you can tell whether a match existed.

For example, if you join employees with departments and an employee has no department assignment, the department columns will show NULL. You can filter for these cases if you want to find incomplete records: WHERE table_b.id IS NULL returns only rows from the left table that had no match.

Left outer join vs. right outer join vs. full outer join

A right outer join does the opposite: it keeps all rows from the right table and adds matching rows from the left. It is rarely used because you can always rewrite it as a left outer join by swapping the table order.

A full outer join keeps all rows from both tables, filling in NULLs wherever there is no match. Not all databases support full outer joins (SQLite does not), but SQL Server and PostgreSQL do. Use a full outer join when both tables are equally important and you want to see everything.

In practice, left outer join is the most common because most queries have a primary table you want to keep complete.

A concrete example with real columns

Imagine you have a customers table with id, name, and email, and an orders table with id, customer_id, and order_date. You want every customer with their order dates:

SELECT customers.name, customers.email, orders.order_date FROM customers LEFT OUTER JOIN orders ON customers.id = orders.customer_id

If Alice bought something on January 15, her row shows: Alice | alice@example.com | 2024-01-15. If Bob never bought anything, his row shows: Bob | bob@example.com | NULL. The result includes all customers, not just the ones who ordered.

If you had used an inner join instead, Bob would not appear in the results at all — only customers with orders would show up.

Frequently Asked Questions

Can a left outer join return duplicate rows?

Yes. If the right table has multiple rows matching a single row in the left table, the left row repeats once for each match. For example, if a customer placed three orders, that customer appears three times in the result — once per order. Use GROUP BY or DISTINCT if you want to avoid duplicates.

What if both tables have a column with the same name?

You need to specify which table the column comes from using the table name: SELECT customers.id, orders.id FROM customers LEFT OUTER JOIN orders. Otherwise the database does not know which id you mean. Many people use aliases to shorten this: SELECT c.id, o.id FROM customers c LEFT OUTER JOIN orders o.

Does the order of tables matter in a left outer join?

Yes, completely. LEFT OUTER JOIN keeps all rows from the first table and adds optional data from the second. If you swap the tables, you get a different result. Swapping to a right outer join gives you the same outcome, but left outer join is clearer because the table order matches the logic.

Can I join more than two tables with left outer join?

Yes. You can chain multiple left outer joins: FROM table_a LEFT OUTER JOIN table_b ON ... LEFT OUTER JOIN table_c ON .... Each join adds more optional columns. The first table stays complete, and each subsequent table adds data where it matches.

What is the difference between LEFT OUTER JOIN and LEFT JOIN?

There is no difference. LEFT JOIN and LEFT OUTER JOIN are the same thing in every major database. The word OUTER is optional and mostly historical. Modern code usually just writes LEFT JOIN because it is shorter.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.