Oracle中两条degreeprogram自连接SQL语句的差异疑问

Great question—this boils down to how Oracle handles self-joins with and without table aliases when using NATURAL JOIN. Let's break this down clearly:

1. The Second Query (With Aliases) Behaves As Expected #

When you run:

select * from degreeprogram d1 NATURAL JOIN degreeprogram d2;

Oracle treats d1 and d2 as separate instances of the same table. Here's what happens:

• NATURAL JOIN automatically identifies all columns with matching names across the two instances (every column in degreeprogram, since it's the same table)
• It creates an equality condition for each matching column: d1.col1 = d2.col1 AND d1.col2 = d2.col2 AND ...
• It also removes duplicate columns from the final result set (since joined columns would otherwise appear twice)

If your degreeprogram table has a primary key (like program_id), this effectively means each row in d1 only matches the exact same row in d2 (since the primary key uniquely identifies each row). That's why you get a result set identical to the original table—exactly what you expected.

2. The First Query (No Aliases) Does Something Unexpected #

Now look at:

select * from degreeprogram NATURAL JOIN degreeprogram ;

The critical difference here is that Oracle doesn't see two separate table instances—instead, it treats both references to degreeprogram as the same single instance. This breaks the NATURAL JOIN logic:

• Since it's the same table, all columns are "matching", but Oracle can't distinguish between the left and right side of the join (no aliases to tell them apart)
• The join condition ends up being something like col1 = col1 for every column—conditions that are always true
• This is functionally equivalent to a CROSS JOIN (Cartesian product): every row in the table is matched with every row (including itself)

If your original table has n rows, this query will return n * n rows—hence why you see each tuple repeated multiple times.

Key Takeaway #

• Always use aliases for self-joins! When you alias the table, Oracle knows to treat them as separate entities, and NATURAL JOIN works as intended.
• Without aliases, Oracle can't differentiate the two table references, turning the natural join into an accidental Cartesian product.

内容的提问来源于stack exchange，提问作者user3579222