10.5 FULL JOIN

Level: 10 | Version: v1.1 | Author: Meptrasoft

Overview

FULL JOIN returns all rows from both tables.

• Matching rows are combined.
• Unmatched left rows remain with NULL on the right.
• Unmatched right rows remain with NULL on the left.

LEFT TABLE
     +
RIGHT TABLE
     ↓
FULL JOIN
     ↓
ALL ROWS FROM BOTH

FULL JOIN → Keep everything from both tables.

Practice Tables

This topic reuses the customers and orders tables from 10.2.

In the original data:

• Some customers have no orders.
• Every existing order has a matching customer.

For example:

Anjali Verma → no order
Rahul Das    → no order
Divya Nair   → no order

Basic FULL JOIN

SELECT c.customer_name, o.order_id
FROM customers c
FULL JOIN orders o
    ON c.customer_id = o.customer_id;

A FULL JOIN keeps:

Matching customers + orders
     +
Customers without orders
     +
Orders without customers

Understanding the Result

Suppose:

Customer 1 → Order 101
Customer 2 → Order 102
Customer 3 → No order
Order 103  → No customer

The result is:

The unmatched row gets NULL on the side where no match exists.

FULL JOIN preserves unmatched rows from both sides.

FULL JOIN vs Other Joins

INNER JOIN → MATCHING ONLY

LEFT JOIN
→ ALL LEFT + MATCHING RIGHT

RIGHT JOIN
→ ALL RIGHT + MATCHING LEFT

FULL JOIN
→ ALL LEFT + ALL RIGHT

This is the easiest way to remember the difference.

Important Note About the Extra Order

The original orders table has a foreign key:

customer_id INT REFERENCES customers(customer_id)

So this would normally fail:

INSERT INTO orders
VALUES (11, '2026-01-30', 9999, 999);

because customer 999 does not exist.

You do not need to break the foreign-key constraint just to understand FULL JOIN. The unmatched customers already demonstrate the part where the left side has no match.

To demonstrate an unmatched right row, you would need a dataset where the relationship is not enforced, such as a separate practice table without that foreign key.

Finding Unmatched Rows

A FULL JOIN can help identify records that exist on only one side.

Conceptually:

LEFT ONLY
   +
MATCHED
   +
RIGHT ONLY

This makes FULL JOIN useful for comparing two datasets and finding missing records.

Common Beginner Mistakes

Placement Quick Points

FULL JOIN
→ ALL LEFT ROWS
→ ALL RIGHT ROWS
→ MATCH WHERE POSSIBLE
→ NO MATCH = NULL

• FULL JOIN keeps every row from both tables.
• Matching rows are combined.
• Unmatched rows remain.
• Missing values appear as NULL on the opposite side.
• It is useful for comparing two datasets and finding unmatched records.

Interview Questions

What is a FULL JOIN?

A FULL JOIN returns all rows from both tables, matching rows where possible.

What happens when there is no match?

The row is still returned, and the columns from the other table contain NULL.

What is the difference between INNER JOIN and FULL JOIN?

TEXT

INNER → Matching rows only
FULL  → All rows from both tables

When is FULL JOIN useful?

It is useful when you need to see all records from both sides, including unmatched records.

Can FULL JOIN show NULL values?

Yes. Unmatched rows contain NULL for columns from the other table.

Practice & Hands-On Exercises

Using customers and orders:

1. Write a FULL JOIN:

SELECT c.customer_name, o.order_id
FROM customers c
FULL JOIN orders o
    ON c.customer_id = o.customer_id;

2. Display customer names and order IDs.
3. Identify customers without orders.
4. Explain where NULL appears in the result.
5. Compare FULL JOIN with INNER JOIN and LEFT JOIN.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

FULL JOIN
    ↓
ALL LEFT ROWS
+
ALL RIGHT ROWS
    ↓
MATCH WHEN POSSIBLE
    ↓
NO MATCH → NULL

FULL JOIN keeps all rows from both tables, combining matches and preserving unmatched rows with NULL values.

End of FULL JOIN