Timescale Logo

What Is a PostgreSQL Left Join? And a Right Join?

The SQL language offers a variety of join types, enabling developers to blend and analyze data from different tables. Among these join types, the right and left joins hold a unique place. Right and left joins are also known as right outer joins and left outer joins.

In fact, “outer join" is a category that includes left outer join, right outer join, and full outer join.

  • A PostgreSQL left outer join returns all the rows from the left table and the matched rows from the right table. If no match is found, the result is NULL on the right side.

  • A PostgreSQL right outer join returns all the rows from the right table and the matched rows from the left table. If no match is found, the result is NULL on the left side.

  • A PostgreSQL full outer join combines the results of both left and right outer joins. It returns all rows from both tables and fills in NULLs for missing matches on either side.

Grasping the Syntax of PostgreSQL Outer Join

The basic syntax for an outer join in PostgreSQL is as follows:

SELECT column_name(s)
FROM table1
ON table1.column_name = table2.column_name;

Here, table1 and table2 are the tables you wish to join, and column_name is the common field between them.

Example of Left Join Usage in PostgreSQL

Let's explore an example that illustrates how a left join functions in PostgreSQL:

customer_id INT,
product_id INT,
order_date DATE);

CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(255));

(1, 101, 1, '2021-01-01'),
(2, 102, 2, '2021-02-01'),
(3, 103, 3, '2021-03-01')

(101, 'John Doe'),
(102, 'Jane Smith');

SELECT customers.customer_name, orders.order_date
FROM customers
ON customers.customer_id = orders.customer_id;

This query will return a list of all customers along with their order dates, including those customers who have made no orders.

seo=# SELECT customers.customer_name, orders.order_date
seo-# FROM customers
seo-# LEFT OUTER JOIN orders
seo-# ON customers.customer_id = orders.customer_id;
customer_name | order_date 
John Doe      | 2021-01-01
Jane Smith    | 2021-02-01
(2 rows)

Real-World Use Cases of Right and Left Joins

Left and right joins can be extremely beneficial in real-world programming scenarios:

  • E-commerce platforms: Combining customer information with purchase orders to track buying behavior, including customers who haven't made any purchases.

  • Project management tools: Merging task assignments with employee details to manage project timelines, including employees without any assigned tasks.

  • Healthcare systems: Linking patient records with medical histories for better diagnosis and treatment, including patients with no prior medical history.

Visualizing Right and Left Joins

Visualize two circles in a Venn diagram, each representing a table. In a left outer join, the result set includes the entire left circle (table) and the intersecting part of the right circle (table). The right outer join is the opposite, and the full outer join includes all parts of both circles.

A SQL right join diagram
Left Join diagram


Mastering outer joins in PostgreSQL allows developers to handle and extract valuable insights from their data more effectively. Understanding and using left and right outer joins can significantly enhance your data analysis capabilities. 

Learn More About Join Strategies to Enhance Your Database Performance

Understanding how the PostgreSQL parser picks a join method can be valuable to define join strategies to enhance your database performance. To learn more about this topic and take your knowledge of joins to another level, check out this article on PostgreSQL Join Type Theory.

Timescale Logo

Subscribe to the Timescale Newsletter

By submitting, I acknowledge Timescale’s Privacy Policy
2024 © Timescale Inc. All rights reserved.