A left join combines rows from two tables, keeping all rows from the left table whether or not they match the right table

When you write a left join in SQL, you're telling the database: "Show me everything from my first table, and attach matching data from the second table wherever it exists. If there's no match, leave that column blank." This is different from an inner join, which only shows rows where both tables have matching data.

The "left" and "right" refer to the order you write the tables in your query. The left table is the one you mention first, after the FROM keyword. The right table is the one you mention after the LEFT JOIN keyword. All rows from the left table appear in your results, even if the right table has no matching row for them.

Key Takeaways

  • A left join returns every row from the left table plus any matching rows from the right table, with blank cells where no match exists.
  • The join happens on a condition you specify — usually matching an ID or name column in both tables.
  • If a row in the left table has no match in the right table, the columns from the right table will show NULL (blank) in the result.
  • Left joins are useful when you want to keep all records from one table and see related data from another table only when it's available.

How the join condition works

The join condition is the rule that decides which rows match. You write it after the ON keyword. Most commonly, you match an ID column from the left table to an ID column in the right table. For example, if you have a customers table and an orders table, you might join them ON customers.id = orders.customer_id.

The database reads this condition and pairs up rows where it's true. If a customer has three orders, that customer's row will appear three times in the result — once for each order. If a customer has no orders, that customer's row appears once, with the order columns showing NULL.

When to use a left join instead of other joins

Use a left join when you want to preserve all records from your primary table and see what related data exists. For instance, if you're analyzing customer data and want to see every customer plus their order history, a left join keeps customers with no orders in your results. An inner join would drop those customers entirely.

If you used a right join instead, you'd flip the logic — you'd keep all rows from the right table and match them to the left. In practice, most people use left joins because it's easier to think about: "I want all of this table, plus extra info from that table if it exists."

What NULL means in left join results

When a row from the left table has no matching row in the right table, the columns from the right table show NULL. NULL is SQL's way of saying "no value" or "unknown." It's not zero, it's not an empty string — it's the absence of data.

This matters when you filter or calculate on those columns later. If you write WHERE orders.id IS NOT NULL, you're asking for only the rows where a match was found. If you write WHERE orders.id IS NULL, you're asking for only the rows where no match existed — the "unmatched" records from the left table.

A concrete example with real column names

Imagine you have a products table with columns: product_id, product_name, price. You also have a sales table with columns: sale_id, product_id, quantity_sold, sale_date. You write:

SELECT products.product_name, products.price, sales.quantity_sold, sales.sale_date FROM products LEFT JOIN sales ON products.product_id = sales.product_id

The result shows every product, even ones that have never been sold. For products with sales, you see the quantity and date. For products with no sales, the quantity_sold and sale_date columns are NULL. If a product has been sold five times, that product's row appears five times in the result, once for each sale.

Left join versus left outer join

In SQL, LEFT JOIN and LEFT OUTER JOIN mean exactly the same thing. The word "outer" is optional. Most people write LEFT JOIN because it's shorter, but you'll see both in real code.

Some databases use slightly different syntax for other operations, but the behavior of a left join is identical across systems: all rows from the left table, matched rows from the right table, NULL where no match exists. You can use either term and get the same result.

Frequently Asked Questions

What's the difference between a left join and an inner join?

An inner join returns only rows where both tables have a match. A left join returns all rows from the left table, whether or not they match the right table. If you use an inner join on customers and orders, customers with no orders disappear. With a left join, they stay in the results with NULL in the order columns.

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

Yes. You can chain left joins together: FROM table_a LEFT JOIN table_b ON ... LEFT JOIN table_c ON ... Each join adds more columns and follows the same rule — all rows from the previous result, matched data from the new table, NULL where no match exists.

How do I filter out the NULL rows after a left join?

Use a WHERE clause with IS NOT NULL. For example, WHERE orders.id IS NOT NULL keeps only rows where a match was found. WHERE orders.id IS NULL keeps only the unmatched rows from the left table.

Does the order of tables matter in a left join?

Yes. LEFT JOIN keeps all rows from the left table. If you swap the table order, you get different results. To keep all customers and show their orders, use customers LEFT JOIN orders. To keep all orders and show their customers, use orders LEFT JOIN customers.

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

You need to specify which table's column you want by writing table_name.column_name in your SELECT list. If you write SELECT * FROM products LEFT JOIN sales ON products.product_id = sales.product_id, both product_id columns appear in the result — one from each table.