askvity

What is left join?

Published in SQL Joins 3 mins read

A LEFT JOIN (or LEFT OUTER JOIN) in SQL retrieves all records from the "left" table and the matching records from the "right" table. If no match is found in the right table, NULL values are used for the right table's columns.

Understanding LEFT JOIN

The fundamental purpose of a LEFT JOIN is to combine rows from two tables based on a related column. It's crucial for scenarios where you want to see all records from one table, even if there isn't a corresponding entry in the other table.

How it Works

  • Left Table: This is the table specified before the LEFT JOIN keyword in your SQL query. All rows from this table will be included in the result.
  • Right Table: This is the table specified after the LEFT JOIN keyword. Only matching rows from this table, based on the join condition, will be included.
  • Matching Rows: If a row in the left table has a matching row in the right table (according to the join condition), the columns from both tables will be combined into a single row in the result set.
  • Non-Matching Rows: If a row in the left table does not have a matching row in the right table, the columns from the left table will be included, and the columns from the right table will be filled with NULL values.

Example

Imagine two tables: Customers and Orders.

Customers Table:

CustomerID CustomerName City
1 John Doe New York
2 Jane Smith London
3 Peter Jones Paris
4 Alice Brown Tokyo

Orders Table:

OrderID CustomerID OrderDate
101 1 2023-01-15
102 1 2023-02-20
103 2 2023-03-10

A LEFT JOIN query like this:

SELECT
    Customers.CustomerID,
    Customers.CustomerName,
    Orders.OrderID,
    Orders.OrderDate
FROM
    Customers
LEFT JOIN
    Orders ON Customers.CustomerID = Orders.CustomerID;

Will produce the following result:

CustomerID CustomerName OrderID OrderDate
1 John Doe 101 2023-01-15
1 John Doe 102 2023-02-20
2 Jane Smith 103 2023-03-10
3 Peter Jones NULL NULL
4 Alice Brown NULL NULL

Notice that even though Peter Jones and Alice Brown don't have any orders in the Orders table, their information is still included in the result, with OrderID and OrderDate being NULL.

When to use LEFT JOIN

  • When you need all the rows from one table, regardless of whether there's a matching row in another table.
  • When you want to find records in one table that do not have corresponding records in another table (by filtering for NULL values in the right table's columns).
  • For creating reports that require a complete list of items from one source, even if related information from another source is missing.

Key Takeaways

  • LEFT JOIN includes all rows from the left table.
  • NULL values are used for the right table's columns when there is no match.
  • It's essential for scenarios needing a complete dataset from one table, regardless of matches in another.

Related Articles