How to Use LEFT JOIN in SQL

SQL LEFT JOIN returns every row from the left table plus any matching rows from the right. When no match exists, the right-side columns are filled with NULLs. Use LEFT JOIN to keep optional data, audit gaps, and build inclusive reports without losing left-table rows.

Learning
June 10, 2025
Galaxy Team
Sign up for the latest notes from our team!
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
SQL LEFT JOIN keeps all left-table rows and brings in right-table data when keys match, filling non-matches with NULLs. Its ideal for optional relationships, auditing missing data, and building summary reports.

How to Use LEFT JOIN in SQL

SQL LEFT JOIN returns every row from the left table and columns from the right table when keys match, supplying NULLs where they don't. Use it whenever you need to preserve left-side data.

What Does LEFT JOIN Do in SQL?

LEFT JOIN keeps all rows from the first (left) table. It attaches columns from the second (right) table when the join condition matches. If no match is found, the right-side columns become NULL, so no left-table data is lost.

How Is LEFT JOIN Different from INNER JOIN?

INNER JOIN returns only rows that satisfy the join condition in both tables. LEFT JOIN adds those matches plus every remaining row from the left table. The difference shows up when the right table lacks related records.

Why Would I Use LEFT JOIN?

Use LEFT JOIN to spot missing related data, create inclusive reports, and build optional one-to-many relationships. Common scenarios include listing customers who placed no orders, products without sales, and employees without timesheets.

What Is the Basic Syntax for LEFT JOIN?

Place the left table first, add LEFT JOIN, the right table, and a join condition inside ON. Example: SELECT * FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;

How Do I LEFT JOIN Multiple Tables?

Chain additional LEFT JOIN clauses. Each new right table keeps all currently selected rows intact. Example: customers → orders → order_items. Every customer still appears, even if they lack orders or items.

How Can I Filter Results After a LEFT JOIN?

Apply filters in the WHERE clause for left-table columns. To filter right-table columns without turning the query into an INNER JOIN, move the condition into the ON clause or wrap it in IS NULL logic.

How Do I Handle NULLs Produced by LEFT JOIN?

Use COALESCE() or IFNULL() to replace NULLs with default values. Alternatively, aggregate with COUNT() and conditional expressions to flag missing data.

When Should I Index for LEFT JOIN?

Create indexes on the join keys in both tables, especially the right-table key, to speed matching. Composite or covering indexes may help when additional filters are present.

What Are Best Practices for LEFT JOIN?

Always qualify columns with table aliases, keep join conditions in ON, filter after joins carefully, and read execution plans to avoid accidental cartesian products.

How Do I LEFT JOIN and Aggregate?

LEFT JOIN first, then GROUP BY the desired column. Aggregations still retain unmatched left-table rows, which will appear with NULL values in aggregate calculations unless handled.

How Do I LEFT JOIN the Same Table Twice?

Assign distinct aliases for each instance and add separate ON clauses. This pattern often supports parent-child relationships or comparison between two roles.

How Can I LEFT JOIN with Subqueries?

Treat the subquery as a derived table. Write it within parentheses, give it an alias, and join as usual. Subqueries allow pre-aggregating or filtering large right-side sets.

How Do I Troubleshoot a LEFT JOIN?

First, validate join keys for uniqueness. Next, inspect NULLs in right-table columns to confirm join direction. Finally, compare row counts between INNER JOIN and LEFT JOIN to detect discrepancies.

Key Takeaways

LEFT JOIN preserves left-table rows, fills in right-table data when available, and writes NULLs for gaps. Its perfect for optional relationships, auditing, and inclusive reporting. Always index join keys, qualify columns, and place right-column filters carefully.

Frequently Asked Questions (FAQs)

Does LEFT JOIN work the same in MySQL, PostgreSQL, and SQL Server?

The core behavior is identical across major databases. Syntax differences are minor, such as identifier quoting.

Which table is considered left?

The table that appears immediately after FROM is the left table. The one after LEFT JOIN is the right table.

Can I replace LEFT JOIN with UNION?

UNION cannot mimic LEFT JOIN directly. LEFT JOIN combines columns horizontally, while UNION stacks rows vertically.

How do I count unmatched rows?

LEFT JOIN, then add WHERE right_table.key IS NULL to isolate rows with no match.

Check out our other posts!

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie
BauHealth Logo
Truvideo Logo