Learn sql Day3: Difference between  left join and full outer join output

Question:

I cannot see any difference in output between left join and full outer join with our example. can u create a new data to see me correct difference between them?

This is actually something that confuses many developers in interviews.

The reason you don’t see any difference is because our current data doesn’t have any “orphan” records in the orders table.

Let’s understand why.

Our Current Data

users

orders

Every order belongs to an existing user.

So there are:

• Users without orders (Bob, Alice)
• No orders without users

That’s why LEFT JOIN and FULL OUTER JOIN appear almost identical.

Let’s Create a Better Example

To see the difference, we need an order that doesn’t match any user.

However…

Our foreign key prevents that.

FOREIGN KEY (user_id)
REFERENCES users(id)

This is a good thing because it maintains data integrity.

So for learning purposes, we’ll create another table without a foreign key.

Step 1

DROP TABLE IF EXISTS orders_demo;
CREATE TABLE orders_demo (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    amount NUMERIC(10,2)
);

Notice:

❌ No foreign key.

Step 2

Insert data

INSERT INTO orders_demo(user_id, amount)
VALUES
(1,100),
(1,200),
(2,300),
(999,400);

Now we have:

users

orders_demo

Notice:

user_id = 999

There is no matching user.

This is our orphan order.

INNER JOIN

SELECT
    u.id,
    u.name,
    o.amount
FROM users u
INNER JOIN orders_demo o
ON u.id = o.user_id;

Result

The orphan order disappears.

LEFT JOIN

SELECT
    u.id,
    u.name,
    o.amount
FROM users u
LEFT JOIN orders_demo o
ON u.id = o.user_id;

Result

Question:

Where is the orphan order?

It is gone!

Why?

Because LEFT JOIN keeps every row from the left table (users). Since there is no user with id = 999, there is nothing on the left to preserve.

FULL OUTER JOIN

SELECT
    u.id,
    u.name,
    o.user_id,
    o.amount
FROM users u
FULL OUTER JOIN orders_demo o
ON u.id = o.user_id;

Result

Now you finally see the difference!

The last row exists only because of FULL OUTER JOIN.