We intended to fetch all customers and their addresses. But observe: We project only columns from the
CUSTOMERtable and we don’t have any predicates at all, specifically not predicates using the
ADDRESStable. So, we’re completely ignoring any contributions from the
ADDRESStable. We never really needed the JOIN in the first place!
And in fact, the optimiser can prove this too, because of the
FOREIGN KEYconstraint on
C.ADDRESS_ID, which guarantees that every
CUSTOMERrecord has exactly one corresponding
ADDRESSrecord. The JOIN does not duplicate, nor remove any
CUSTOMERrows, so it is unneeded and thus eliminated (by some, not all databases, will list each database at the end of the article).
So, the database can rewrite the SQL statement to the following, equivalent SQL statement in the presence of said FOREIGN KEY
Read on for a comparison across different products as well.