Is left outer join equivalent to inner join for non-null foreign keys?

Question

Each city has owning country:

create table COUNTRY 
(
     ID number not null,
     NAME varchar,
     primary key (ID)
);

create table CITY 
(
     ID number not null,
     NAME varchar,
     COUNTRY_ID number not null,
     primary key (ID)
);

alter table CITY 
    add constraint CITY_COUNTRY_FK
        foreign key (COUNTRY_ID) references COUNTRY (ID);

Is the following:

select * 
from CITY c
left outer join COUNTRY ctr on ctr.ID = c.COUNTRY_ID
where ...;

equivalent to:

select * 
from CITY c
inner join COUNTRY ctr on ctr.ID = c.COUNTRY_ID
where ...;

because COUNTRY_ID is foreign key and not null?

Answer 1

In this case, the left join is redundant based on the data model. The NOT NULL constraint means that every city has a country_id. The foreign key constraint means that the country_ids are valid and are in the country table.

Combined, these constraints say that every row in city has a matching row in country. When all keys match, the left join is equivalent to inner join.