If you have ever read SQL documentation, studied query examples, or worked with a team of developers, you have likely encountered both LEFT JOIN and LEFT OUTER JOIN. They appear in books, tutorials, and production code — sometimes interchangeably, sometimes as if they mean different things. This naturally raises the question: are they actually different?
The short answer is no. In SQL, LEFT JOIN and LEFT OUTER JOIN
are completely identical. Every major relational database system — including
MySQL, PostgreSQL, SQL Server, Oracle, and SQLite — treats them as the same
operation. The word OUTER is simply optional syntax.
This article explains exactly why they are equivalent, how a
left join works under the hood, where the terminology comes from, and how it
compares to other SQL join types. Whether you are a beginner trying to
understand the basics or an experienced developer seeking clarity, this guide
covers everything you need.
|
Key • LEFT JOIN and LEFT OUTER JOIN are • All major database systems (MySQL, • A LEFT JOIN returns all rows from the • LEFT JOIN is different from INNER • Most modern style guides prefer LEFT • The SQL standard defines LEFT OUTER |
The Direct Answer: LEFT JOIN and LEFT OUTER JOIN Are the Same
In SQL, the keyword OUTER in LEFT OUTER JOIN is entirely
optional. The SQL standard itself defines this join type as a LEFT OUTER JOIN,
but it also allows the shorthand LEFT JOIN as an equivalent alias. This means:
•
LEFT JOIN and LEFT OUTER JOIN produce identical query results.
•
They are parsed by the SQL engine as the same operation.
•
There is no performance difference between the two.
•
You may use either form in any SQL-compliant database without concern.
What Is a LEFT JOIN (or LEFT OUTER JOIN)?
A LEFT JOIN is a type of SQL join that returns all rows from
the left (first) table in the query, regardless of whether there is a matching
row in the right (second) table. When a match exists, columns from the right
table are populated with the matched values. When no match exists, the
right-table columns in the result are filled with NULL.
Basic Syntax
Both of the following queries are functionally identical:
|
— Using LEFT JOIN (shorthand) SELECT orders.order_id, customers.customer_name FROM orders LEFT JOIN customers ON orders.customer_id = customers.id; — Using LEFT OUTER JOIN (full form) SELECT orders.order_id, customers.customer_name FROM orders LEFT OUTER JOIN customers ON orders.customer_id = |
Both queries instruct the database to return every row from
the orders table, and for each row, attempt to find a matching row in the
customers table based on the ON condition. Rows in orders that have no matching
customer will still appear — with NULL in the customer_name column.
|
Syntax |
Example |
|
LEFT JOIN |
SELECT * FROM |
|
LEFT OUTER |
SELECT * FROM |
How a LEFT JOIN Works: Step by Step
Understanding how the SQL engine processes a LEFT JOIN helps
clarify why both syntax forms produce the same result. Here is a simplified
breakdown of the process:
1.
The query engine reads all rows from the left table.
2.
For each row in the left table, it attempts to find matching rows in the
right table using the join condition (specified after ON).
3.
If one or more matches are found, the result set includes rows combining
data from both tables.
4.
If no match is found, the result set still includes the left-table row,
but all right-table columns are set to NULL.
5.
The final result is returned with all left-table rows included, whether
matched or not.
Practical Example
Consider two tables: employees and departments.
|
— employees table | emp_id | name | dept_id | |——–|———|———| | 1 | Alice | 10 | | 2 | Bob | 20 | | 3 | Carol | NULL | — departments table | dept_id | dept_name | |———|————-| | 10 | Engineering | | 20 | Marketing | | 30 | HR | |
|
SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id; |
Result:
|
| name | dept_name | |——-|————-| | Alice | Engineering | | Bob | Marketing | | Carol | NULL | |
Carol has no department assigned, so dept_name is NULL. The HR
department (dept_id 30) does not appear because it has no matching employee —
only left-table rows are guaranteed in a LEFT JOIN. Replacing LEFT JOIN with
LEFT OUTER JOIN in the query above produces exactly this same result.
Why Does the OUTER Keyword Exist?
The word OUTER comes from relational algebra and the formal
SQL standard. In theory, joins can be classified as either inner joins or outer
joins:
•
Inner joins return only matched rows.
•
Outer joins return matched rows plus unmatched rows (with NULLs) from one
or both tables.
The SQL standard uses LEFT OUTER JOIN, RIGHT OUTER JOIN, and
FULL OUTER JOIN as the formal designations. The OUTER keyword was included to
explicitly signal that the join is of the outer variety — meaning it includes
unmatched rows.
Over time, SQL implementors recognized that the OUTER keyword
was redundant when preceded by LEFT, RIGHT, or FULL, since these words already
define the direction of the outer behavior. As a result, OUTER became optional
in all major databases.
|
Historical Context The SQL-92 standard |
Database Compatibility: Which Systems Support Both Forms?
Both LEFT JOIN and LEFT OUTER JOIN are supported in all major
SQL database systems. The table below summarizes compatibility across commonly
used platforms:
|
Database |
LEFT JOIN |
LEFT OUTER |
Preferred |
|
MySQL |
Yes |
Yes |
LEFT JOIN |
|
PostgreSQL |
Yes |
Yes |
LEFT JOIN |
|
SQL Server |
Yes |
Yes |
Either |
|
Oracle |
Yes |
Yes |
Either |
|
SQLite |
Yes |
Yes |
LEFT JOIN |
|
BigQuery |
Yes |
Yes |
LEFT JOIN |
In every system listed above, the two forms are parsed
identically. There is no scenario in any of these databases where choosing one
over the other changes the query’s behavior or execution plan.
LEFT JOIN vs Other SQL Join Types
Understanding how LEFT JOIN compares to other joins helps you
choose the right tool for each situation. The table below summarizes the key
differences:
|
Join Type |
Rows |
NULL |
|
LEFT JOIN / |
All rows from |
Yes — for |
|
INNER JOIN |
Only matching |
No |
|
RIGHT JOIN / |
All rows from |
Yes — for |
|
FULL JOIN / |
All rows from |
Yes — for any |
LEFT JOIN vs INNER JOIN
An INNER JOIN is the most commonly used join type. It returns
only rows where there is a match in both tables. If a row in the left table has
no corresponding row in the right table, it is excluded entirely from the
result. In contrast, a LEFT JOIN always includes every row from the left table.
|
— INNER JOIN: only matched rows SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id; — Carol is excluded (no dept_id match) — LEFT JOIN: all left-table rows SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id; — Carol appears with NULL dept_name |
LEFT JOIN vs RIGHT JOIN
A RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of a
LEFT JOIN. It returns all rows from the right table, plus any matching rows
from the left table. In practice, RIGHT JOINs are rarely used because any right
join can be rewritten as a left join by swapping the table order, which is
generally considered more readable.
LEFT JOIN vs FULL OUTER JOIN
A FULL OUTER JOIN (or FULL JOIN) returns all rows from both
tables, including unmatched rows from each side. Columns from the table that
has no match are filled with NULL. It is effectively a combination of a LEFT
JOIN and a RIGHT JOIN.
|
Quick Tip To find rows in the left |
When to Use LEFT JOIN
A LEFT JOIN is generally the right choice when you want to
retrieve all records from a primary table while optionally pulling in related
data from a secondary table. Common use cases include:
•
Retrieving all customers and their orders, including customers who have
placed no orders.
•
Listing all products and their associated categories, including products
with no category assigned.
•
Generating reports that must include all users whether or not they have
completed a related action.
•
Identifying unmatched records (anti-join pattern) using a WHERE IS NULL
condition.
•
Building dashboards where the absence of related data is meaningful and
should be visible.
When NOT to Use LEFT JOIN
A LEFT JOIN may not be the best choice when:
•
You only want rows where a match exists in both tables — in that case,
INNER JOIN is more appropriate and generally performs better.
•
The presence of NULL values in the result set would cause logic errors
in downstream calculations, such as SUM or AVG aggregations that should exclude
unmatched rows.
•
You inadvertently create a many-to-many relationship between tables,
causing unexpected row duplication in the result.
Style Guide: Which Syntax Should You Use?
Since they are functionally identical, the choice between them is primarily one of style and convention.
Here are a few considerations:
Use LEFT JOIN when:
•
You want concise, modern SQL that is easy to read at a glance.
•
Your team or style guide prefers brevity.
•
You are writing queries for open-source or community-facing projects
where readability is important.
Use LEFT OUTER JOIN when:
•
You are writing code for an audience that may include non-SQL developers
or stakeholders, and you want the query to be more self-documenting.
•
Your organization’s SQL coding standards explicitly specify LEFT OUTER
JOIN for consistency with the SQL standard.
•
You are working with legacy systems or codebases that already use the
full OUTER form throughout.
Conclusion
The question of LEFT OUTER JOIN vs LEFT JOIN has a
straightforward answer: they are the same thing. The keyword OUTER is optional
in SQL and carries no additional meaning when used with LEFT. Every major
relational database — MySQL, PostgreSQL, SQL Server, Oracle, SQLite, and
BigQuery — treats these two forms as completely equivalent at both the syntax
parsing and query execution levels.
Choosing between them is purely a matter of style. Most modern
SQL practitioners prefer LEFT JOIN for its brevity, while some teams favor LEFT OUTER JOIN for its alignment with the formal SQL standard and its explicitness
for readers less familiar with SQL shorthand.
What matters most is consistency. Pick one convention,
document it in your team’s coding standards, and apply it uniformly.
