Is there a way to make a "natural join" with a table having a foreign key on another table?

Question

This is just a dummy example. In reality I have a foreign key which references a lot of more columns. That why I'm trying replace the normal join with a "natural join"

I have a table which has a foreign key on another table. The columns don't have the same name.

I would like to avoid writing join on this_column_of_tableA= this_column_of_tableB

If the foreign key of tableB had the same name of the referencing column, I could do a natural join.

I have tried nonetheless. As expected it didn't work. (cross product)

But Oracle should know which column to use to make the join. Because it is the table definition.

Is this possible to make a jointure in this case without rewriting which column matchs which column. It's prone making errors and a waste of time.

create table TB (  
  TB_1          number,  
  constraint fk_TA foreign key (TB_1)
  REFERENCES TA(TA_1)
);

create table TA (  
  TA_1          number,  
  constraint pk_departments primary key (TA_1)  
);

INSERT INTO TA (TA_1)
   VALUES (1);
   
INSERT INTO TA (TA_1)
   VALUES (2);
   
INSERT INTO TA (TA_1)
   VALUES (3);
   
INSERT INTO TB(TB_1)
   VALUES (1);

INSERT INTO TB(TB_1)
   VALUES (2);
   
select * from TA natural join TB

code

"Natural join" doesn't care about foreign keys, it just blindly matches common column names, which is rarely useful. There is no foreign-key-aware join syntax out of the box, I'm afraid. — William Robertson, May 02 '22 at 09:17

score 2 · Answer 1 · answered May 02 '22 at 07:22

No, there is no simple way to do this and you should avoid this. Natural join is very risky and should be prevented whenever possible. Natural joins require both the identic name and data type of the columns. This means they fail whenever this is changed in just one of the joined tables and then you have to find out where and what. Correct code is code that works without risks and works correctly and not the shortest possible code. Maybe you could create a workaround on a very unpleasant way to make it possible, but I recommend to just use explicit join.

score 1 · Answer 2 · answered May 05 '22 at 20:52

You can do something like this:

WITH A AS (SELECT TA_1 FROM TA)
, B AS (SELECT TB_1 TA_1 FROM TB)
SELECT TA_1 FROM A NATURAL JOIN B;

A fundamental feature of relational databases is that joins are defined by queries, never by the schema. You should generally specify the columns required from your tables and not rely on SELECT *.

Is there a way to make a "natural join" with a table having a foreign key on another table?

2 Answers2