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

They are the same. LEFT JOIN and LEFT OUTER JOIN are synonymous in all major SQL databases. The word OUTER is optional and can be omitted without changing the behavior.

What does NULL mean in a LEFT OUTER JOIN result?

NULL in a LEFT OUTER JOIN result means there was no matching row in the right table for that left-table row. For example, if a store in the Geography table has no transactions in Store_Information, its Sales column will be NULL — indicating no data, not zero.

When should I use LEFT OUTER JOIN instead of INNER JOIN?

Use LEFT OUTER JOIN when you need all rows from the left table regardless of whether they have a match in the right table — for example, listing all customers even those with no orders. Use INNER JOIN when you only want rows that have matches in both tables.

Can I filter a LEFT OUTER JOIN result to find unmatched rows only?

Yes. Add WHERE right_table.column IS NULL after the LEFT OUTER JOIN to return only the rows from the left table that have no match in the right table. This is a common technique to find 'orphan' records.

SQL Left Outer Join

SQL > SQL JOIN > Left Outer Join

In an left outer join, all rows from the first table mentioned in the SQL query are selected, regardless whether there is at least one matching row on the second table in the SQL query. Let's assume that we have the following two tables,

LEFT OUTER JOIN (or simply LEFT JOIN) returns every row from the left table, plus matched rows from the right table. When no match exists in the right table, NULL is returned for the right-table columns — making it ideal for seeing all records from one table even when some have no related data.

Table Store_Information

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
Los Angeles	300	Jan-08-1999
Boston	700	Jan-08-1999

Table Geography

Region_Name	Store_Name
East	Boston
East	New York
West	Los Angeles
West	San Diego

We want to find out sales by store, and we want to see the results for all stores regardless whether there is a sale in the Store_Information table. To do this, we can use the following SQL statement using LEFT OUTER JOIN:

SELECT A1.Store_Name STORE, SUM(A2.Sales) SALES
FROM Geography A1
LEFT OUTER JOIN Store_Information A2
ON A1.Store_Name = A2.Store_Name
GROUP BY A1.Store_Name;

Result:

STORE	SALES
Los Angeles	1800
San Diego	250
New York	NULL
Boston	700

By using LEFT OUTER JOIN, all four rows in the Geography table are listed. Since there is no match for "New York" in the Store_Information table, the Sales total for "New York" is NULL. Note that it is NULL and not 0, as NULL indicates there is no match.

Frequently Asked Questions

Is LEFT JOIN the same as LEFT OUTER JOIN?

Yes. LEFT JOIN and LEFT OUTER JOIN are interchangeable in all major SQL databases (MySQL, PostgreSQL, SQL Server, Oracle, SQLite). The keyword OUTER is optional — both produce identical results.

How do I find rows in the left table that have no match in the right table?

Use a LEFT OUTER JOIN and then filter for NULL in a right-table column: SELECT * FROM Geography A1 LEFT OUTER JOIN Store_Information A2 ON A1.Store_Name = A2.Store_Name WHERE A2.Store_Name IS NULL; This returns only stores in Geography that have no sales records.

Does the order of tables in LEFT OUTER JOIN matter?

Yes. The left table (the one listed before LEFT OUTER JOIN) is the one from which all rows are returned. If you swap the tables, you change which side keeps all its rows. A LEFT JOIN of A → B is not the same as a LEFT JOIN of B → A.

Can I LEFT JOIN more than two tables in a single query?

Yes. You can chain multiple LEFT OUTER JOINs: FROM table1 LEFT JOIN table2 ON ... LEFT JOIN table3 ON ... Each join adds the next table while preserving all rows from the left side accumulated so far.

Next: SQL Cross Join

This page was last updated on March 19, 2026.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.