Open In App

PostgreSQL – LEFT JOIN

Improve
Improve
Like Article
Like
Save
Share
Report

The PostgreSQL LEFT JOIN returns all the rows of the table on the left side of the join and matching rows for the table on the right side of the join. The rows for which there is no matching row on the right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN.

Syntax:
SELECT table1.column1, table1.column2, table2.column1, ....
FROM table1 
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;


table1: First table.
table2: Second table
matching_column: Column common to both the tables.

Let’s analyze the above syntax:

  • Firstly, using the SELECT statement we specify the tables from where we want the data to be selected.
  • Second, we specify the main table.
  • Third, we specify the table that the main table joins to.

The below Venn Diagram illustrates the working of PostgreSQL LEFT JOIN clause:

For the sake 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.

Now, let’s look into a few examples.

Example 1:
Here we will use the LEFT JOIN clause to join the “film” table to the “inventory” table.

SELECT
    film.film_id,
    film.title,
    inventory_id
FROM
    film
LEFT JOIN inventory ON inventory.film_id = film.film_id;

Output:

Example 2:
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.

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:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads