PostgreSQL – LEFT JOIN
In PostgreSQL, the LEFT JOIN (or LEFT OUTER JOIN) is a powerful tool that allows you to merge data from two tables based on a related column. With a LEFT JOIN, you get all records from the “left” table and matching records from the “right” table. If there’s no match in the right table, NULL values will fill the corresponding columns.
In this article, we will explain the PostgreSQL LEFT JOIN in detail, covering its syntax, use cases, and examples to help us understand how to implement it effectively
What is PostgreSQL LEFT JOIN?
A LEFT JOIN in PostgreSQL returns:
- All rows from the first (left) table.
- Matching rows from the second (right) table.
- NULL values for any columns in the right table that don’t have a corresponding match.
This makes LEFT JOIN especially useful for identifying unmatched data between tables, such as records that don’t have related data in another table
Syntax
SELECT table1.column1, table1.column2, table2.column1, ....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
Key Terms
- table1: The primary table from which you want to select data.
- table2: The secondary table that you want to join with the primary table.
- matching_column: The common column between the two tables on which the join condition is based.
Visualizing LEFT JOIN with a Venn Diagram
A Venn diagram can visually illustrate how a LEFT JOIN works. The left circle represents the rows from the primary table, while the right circle represents the rows from the secondary table. The intersection shows the matching rows, while the left part of the left circle shows rows that have no match in the secondary table, resulting in NULL values for those columns.
The below Venn Diagram illustrates the working of PostgreSQL LEFT JOIN clause:
For understanding of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.
Examples of PostgreSQL LEFT JOIN
Let us take a look at some of the examples of LEFT JOIN in PostgreSQL to better understand the concept. In this database, we have two tables: film and inventory. Let’s examine how the LEFT JOIN clause can be applied to these tables.
Example 1: Basic LEFT JOIN
Here we will use the LEFT JOIN clause to join the “film” table to the “inventory” table. In this example, we’ll retrieve all films from the film table and their corresponding inventory IDs from the inventory table.
Query:
SELECT
film.film_id,
film.title,
inventory_id
FROM
film
LEFT JOIN inventory ON inventory.film_id = film.film_id;
Output
Explanation:
This query returns all films from the “film” table and their corresponding inventory IDs. If a film has no inventory, the ‘inventory_id' will be NULL. For instance, “Movie B” has no inventory, so inventory_id is NULL.
Example 2: Filtering with LEFT JOIN
Here we will use the LEFT JOIN clause to join the “film” table to the “inventory” table and use the WHERE clause to filter out films that are not in the inventory supply.
Query:
SELECT
film.film_id,
film.title,
inventory_id
FROM
film
LEFT JOIN inventory ON inventory.film_id = film.film_id
WHERE
inventory.film_id IS NULL;
Output
Explanation:
This query returns all films that are not present in the inventory. It helps in identifying films that are not available for rent. Since inventory.film_id is NULL for “Movie B,” it appears in the output, indicating it’s not available in inventory.
Important Points About PostgreSQL LEFT JOIN
- Multiple LEFT JOINs: When using multiple LEFT JOINs in a single query, the order of the joins can affect the result set.
- Using WHERE Clause with LEFT JOIN: While you can use the WHERE clause for joining tables (implicit join), it’s recommended to use the explicit LEFT JOIN syntax for clarity and maintainability.
- LEFT JOIN can be used in self-joins to query hierarchical or recursive relationships within a single table.
- Explicit LEFT JOIN syntax improves query readability, making it easier to understand and maintain, especially in complex queries.
Conclusion
In PostgreSQL, the LEFT JOIN is an essential type of join that enables the retrieval of all rows from one table, even if there are no matching entries in the joined table. This makes it particularly valuable when working with data analysis tasks where we need to highlight unmatched rows or identify missing data across tables. The PostgreSQL LEFT JOIN is ideal for reports, audits, and detailed data checks, as it offers flexibility in querying and improves data visibility.
FAQs
How does LEFT JOIN work in PostgreSQL?
In PostgreSQL, a LEFT JOIN returns all rows from the left (first) table and matches rows from the right (second) table where possible. If no match is found, NULL values fill the right table’s columns.
How do you know when to use LEFT JOIN?
Use a LEFT JOIN when you want all records from one table regardless of matching entries in the second table, especially to highlight rows that have no corresponding data in the other table.
Why use LEFT JOIN instead of JOIN?
LEFT JOIN is useful when you need to include all entries from the left table, even if they lack a match in the right table. This helps in identifying missing or unmatched data, which an INNER JOIN would exclude
