PostgreSQL FULL JOIN

Combine all records from both tables with matching and non-matching rows

🔗 What is FULL JOIN?

FULL JOIN (or FULL OUTER JOIN) returns all records from both tables. It combines LEFT JOIN and RIGHT JOIN, showing NULL where matches don't exist. 


-- Basic FULL JOIN syntax
SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Key FULL JOIN Concepts

🔄

All Records

Returns rows from both tables 

SELECT * FROM customers
FULL JOIN orders ON customers.id = orders.customer_id;
🎯

Matched Rows

Shows matching records from both sides 

-- Rows with matches in both tables
SELECT c.name, o.order_id
FROM customers c
FULL JOIN orders o ON c.id = o.customer_id;
❌

NULL Both Sides

NULL appears for unmatched records 

-- Customers without orders: NULL in orders
-- Orders without customers: NULL in customers
SELECT * FROM customers c
FULL JOIN orders o ON c.id = o.customer_id;
🔍

Find All Gaps

Identify unmatched records from either table 

SELECT * FROM customers c
FULL JOIN orders o ON c.id = o.customer_id
WHERE c.id IS NULL OR o.order_id IS NULL;

🔹 Basic FULL JOIN Example

FULL JOIN retrieves all customers and all orders, regardless of whether they match. This is useful for comprehensive data analysis where you need to see everything from both tables. 

-- Sample tables
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    product VARCHAR(100)
);

-- FULL JOIN query
SELECT 
    c.id,
    c.name,
    o.order_id,
    o.product
FROM customers c
FULL JOIN orders o ON c.id = o.customer_id;

Output:

id name order_id product
1 John Doe 101 Laptop
2 Jane Smith NULL NULL
NULL NULL 102 Mouse

🔹 Finding Unmatched Records

Use FULL JOIN with WHERE clause to find records that exist in one table but not the other. This helps identify data inconsistencies or orphaned records in your database. 

-- Find customers without orders AND orders without customers
SELECT 
    c.id AS customer_id,
    c.name,
    o.order_id,
    o.product
FROM customers c
FULL JOIN orders o ON c.id = o.customer_id
WHERE c.id IS NULL OR o.order_id IS NULL;

Output:

customer_id name order_id product
2 Jane Smith NULL NULL
NULL NULL 102 Mouse

🔹 FULL JOIN with COALESCE

COALESCE function replaces NULL values with meaningful defaults. This makes FULL JOIN results more readable by providing fallback values when data is missing from either table. 

-- Replace NULL values with default text
SELECT 
    COALESCE(c.name, 'No Customer') AS customer_name,
    COALESCE(o.product, 'No Product') AS product_name,
    COALESCE(o.order_id::TEXT, 'N/A') AS order_number
FROM customers c
FULL JOIN orders o ON c.id = o.customer_id;

Output:

customer_name product_name order_number
John Doe Laptop 101
Jane Smith No Product N/A
No Customer Mouse 102

🧠 Test Your Knowledge

What does FULL JOIN return?