LEFT JOIN and RIGHT JOIN in PostgreSQL

Introduction

LEFT JOIN and RIGHT JOIN help you combine rows from two tables based on a related column, even if some rows don't have matching data in the other table.

You want to list all customers and their orders, including customers who have not placed any orders yet.

You need to find all employees and their departments, even if some employees are not assigned to any department.

You want to see all products and their sales, including products that have never been sold.

You want to compare two lists and keep all items from one list, showing related info if available.

Syntax

PostgreSQL

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;

LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match, right table columns are NULL.

RIGHT JOIN returns all rows from the right table and matching rows from the left table. If no match, left table columns are NULL.

Examples

Shows all customers and their orders. Customers without orders still appear with NULL for order id.

PostgreSQL

SELECT customers.name, orders.id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Shows all departments and their employees. Departments without employees still appear with NULL for employee name.

PostgreSQL

SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

Sample Program

This example creates two tables: customers and orders. It inserts some data, then shows how LEFT JOIN lists all customers with their orders (or NULL if none), and RIGHT JOIN lists all orders with their customers (or NULL if none).

PostgreSQL

CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO orders VALUES (101, 1), (102, 1), (103, 3);

-- LEFT JOIN example
SELECT customers.name AS customer, orders.id AS order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
ORDER BY customers.id, orders.id;

-- RIGHT JOIN example
SELECT customers.name AS customer, orders.id AS order_id
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id
ORDER BY orders.id;

Important Notes

LEFT JOIN and RIGHT JOIN are just mirror images; you can usually rewrite a RIGHT JOIN as a LEFT JOIN by switching table order.

If you only want rows where both tables match, use INNER JOIN instead.

NULL values in joined columns mean no matching row was found in the other table.

Summary

LEFT JOIN keeps all rows from the left table, adding matching rows from the right table or NULLs if no match.

RIGHT JOIN keeps all rows from the right table, adding matching rows from the left table or NULLs if no match.

Use these joins to include unmatched rows from one side when combining tables.