PostgreSQL – LEFT JOIN
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:
Contact Us