Published at Mar 6, 2024
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.
The basic syntax for an outer join in PostgreSQL is as follows:
SELECT column_name(s)
FROM table1
[LEFT | RIGHT | FULL] OUTER JOIN table2
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.
Let's explore an example that illustrates how a left join functions in PostgreSQL:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
order_date DATE);
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(255));
INSERT INTO orders VALUES
(1, 101, 1, '2021-01-01'),
(2, 102, 2, '2021-02-01'),
(3, 103, 3, '2021-03-01')
INSERT INTO customers VALUES
(101, 'John Doe'),
(102, 'Jane Smith');
SELECT customers.customer_name, orders.order_date
FROM customers
LEFT OUTER JOIN orders
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)
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.
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.
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.
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.