LEFT JOIN in Postgres
Introduction
When you need to perform a query that combines and returns data from more than one table, a JOIN can help you get the job done. However, it’s important to know which type of join to use when you’re formulating your query– a LEFT JOIN or a RIGHT JOIN. In this article, we’ll focus our attention on the LEFT JOIN in Postgres. We’ll discuss how this type of join works and provide some examples of its use.
Prerequisite
In order to follow along with our examples and get the most out of this tutorial, you’ll need to have PostgreSQL installed and configured on your machine.
What is a Postgres LEFT JOIN?
Let’s begin our discussion by taking a closer look at the LEFT JOIN and how it works. The LEFT JOIN in Postgres allows you to join two tables– we’ll refer to them as the “left” and “right” tables. The “left” table is the one referenced by the FROM keyword in the query, and the “right” table” is the one referenced by the JOIN keyword. When a LEFT JOIN occurs in a query, rows that match the defined query conditions will be retrieved from the right table, but even unmatched rows are retrieved from the left table.
LEFT JOIN Example
The definition of a LEFT JOIN may sound a bit confusing, so let’s look at some examples. In this section, we’ll check out some typical queries that utilize a LEFT JOIN in Postgres.
Our first example involves a scenario where we have tables ‘x’ and ‘y’. A foreign key relationship exists between table ‘y’ and table ‘x’. In the context of our query, table ‘x’ will be the left table and table ‘y’ will be the right table.
Each row in table ‘x’ may or may not have related rows in the ‘y’ table. Each row in the ‘y’ table has a one-to-one relationship with a row in the ‘x’ table.
In order to select rows from the ‘x’ table that have complementary rows in table ‘y’, we’ll use a JOIN clause. You’ll see that if there’s a row in table ‘x’ that doesn’t have a related row in table ‘y’, the values for columns that come from table ‘y’ will be NULL.
Let’s take a look at the following tables:
TABLE X
| X |
|---|
| PK_X : INT |
| co1: CHAR(2) |
TABLE Y
| Y |
|---|
| PK_Y : INT |
| co1: CHAR(2) |
| col2: CHAR(2) |
| FK_X : INT |
NOTE: FK is the abbreviation for Foreign Key.
1 2 3 4 5 6 7 8 | SELECT x.pk_x, x.col1, y.pk_x, x.col2 FROM X LEFT JOIN Y ON X .pk_x = y.fk_x; |
There are a few important things to note in this query:
First, we specify the columns from which we want to retrieve data using the SELECT keyword.
Then we specify the name of the table from which we’d like to retrieve all rows using the FROM clause. This is the “left” table.
Next, we specify the name of the “right” table after the LEFT JOIN clause. We also define the condition on which we join the two tables.
Now that we’ve studied a very simple example, let’s look at a more realistic application of the LEFT JOIN clause in a query.
We’ll need a sample dataset to use for this query, so we’ll first create two tables and insert records into them:
1 2 3 4 5 | CREATE TABLE product ( product_id INT PRIMARY KEY, name VARCHAR (100), description VARCHAR (100) ); |
1 2 3 4 5 | CREATE TABLE stock ( stock_id INT PRIMARY KEY, product_id INTEGER, on_stock INTEGER ); |
1 2 3 4 5 6 7 | INSERT INTO product (product_id, name, description) VALUES (1, 'Han Pork & Beans', 'preserved beans and pork in a can'), (2, 'Clara Ole cream cheese', 'spaghetti sauce'), (3, 'Royal Pasta', 'spaghetti noodles'), (4, 'Century Tuna', 'Tuna in a can'), (5, 'Vienna Sausage', 'sausage created in vienna'); |
1 2 3 4 5 6 7 | INSERT INTO stock (stock_id, product_id, on_stock) VALUES (1001, 1, 200), (1002, 2, 250), (1003, 3, 500), (1004, 4, 25), (1005, 5, 672); |
At this point, the contents of our tables should look something like the following:
PRODUCT TABLE
1 2 3 4 5 6 7 8 | product_id | name | description ------------+------------------------+----------------------------------- 1 | Han Pork & Beans | preserved beans and pork in a can 2 | Clara Ole cream cheese | spaghetti sauce 3 | Royal Pasta | spaghetti noodles 4 | Century Tuna | Tuna in a can 5 | Vienna Sausage | sausage created in vienna (5 rows) |
STOCK
1 2 3 4 5 6 7 8 | stock_id | product_id | on_stock ----------+------------+---------- 1001 | 1 | 200 1002 | 2 | 250 1003 | 3 | 500 1004 | 4 | 25 1005 | 5 | 672 (5 rows) |
Now, let’s retrieve data from those tables in a query that uses a LEFT JOIN:
1 2 3 4 5 6 7 | SELECT product.product_id, product.name, stock_id FROM product LEFT JOIN stock ON stock.product_id = stock.stock_id; |
The result should look like this:
1 2 3 4 5 6 7 8 | product_id | name | stock_id ------------+------------------------+---------- 1 | Han Pork & Beans | 2 | Clara Ole cream cheese | 3 | Royal Pasta | 4 | Century Tuna | 5 | Vienna Sausage | (5 rows) |
Conclusion
There are a few different types of JOINs you can use in PostgreSQL, and it’s important to understand how each one works in order to use the correct one in your queries. In this article, we talked about the LEFT JOIN in Postgres and reviewed some examples to understand how it’s used. You can use these instructions and examples as a guide to incorporate the LEFT JOIN clause into your own PostgreSQL queries.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started