PostgreSQL LEFT JOIN
Retrieve all records from the left table with matching records from the right
🔗 What is LEFT JOIN?
LEFT JOIN returns all records from the left table and matching records from the right table. If no match exists, NULL values appear for right table columns.
-- Basic LEFT JOIN syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
Key LEFT JOIN Concepts
All Left Records
Returns every row from left table
SELECT * FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;Matching Records
Includes matching rows from right table
SELECT c.name, o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;NULL Values
Shows NULL when no match found
-- Customers without orders show NULL
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;Find Unmatched
Identify records without matches
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_id IS NULL;🔹 Basic LEFT JOIN Example
Let's join customers with their orders. This query retrieves all customers, including those who haven't placed any orders yet. The LEFT JOIN ensures no customer is excluded from the results.
-- Sample tables
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT,
order_date DATE
);
-- LEFT JOIN query
SELECT
c.id,
c.name,
o.order_id,
o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;Output:
| id | name | order_id | order_date |
|---|---|---|---|
| 1 | John Doe | 101 | 2024-01-15 |
| 2 | Jane Smith | NULL | NULL |
🔹 LEFT JOIN with WHERE Clause
Combine LEFT JOIN with WHERE to filter results. You can find customers without orders by checking for NULL values in the joined table columns.
-- Find customers who haven't placed orders
SELECT
c.id,
c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_id IS NULL;Output:
| id | name |
|---|---|
| 2 | Jane Smith |
🔹 Multiple LEFT JOINs
You can chain multiple LEFT JOINs to combine data from three or more tables. Each LEFT JOIN preserves all records from the previous result set.
-- Join customers, orders, and products
SELECT
c.name AS customer_name,
o.order_date,
p.product_name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN products p ON o.product_id = p.id;Output:
| customer_name | order_date | product_name |
|---|---|---|
| John Doe | 2024-01-15 | Laptop |
| Jane Smith | NULL | NULL |