How to do natural join when the common column have different names?

Question

I need to do natural join on two tables named Customers and Addresses (relationship 1:1), the common column in those tables is the key- ID (according to that column the natural join will operate) . however- this Column in table Customer is called "ID_Customer", and in the table Addresses it's called- "ID".

Because of that, the natural join doesn't work correctly, because the program doesn't identify that it's the same column (by significance).

I can not change the columns names to be the same (because of many reasons..) is there any way I could make this work- so the program would understand those are the same columns?

Answer 1

So don't use natural join. Do an explicit join instead:

from Customer c join
     Address a
     on a.id = c.id_Customer

Also, I wouldn't be surprised if the actual join condition were:

     on a.id_Customer = c.id

(When using id as the primary key of tables, a good practice is to include the table name in the foregn reference.)

As a general rule, natural joins are a bad choice in the long term. You might store such queries in stored procedures, triggers, or applications. Then someone modifies the table structure -- adds, removes, or renames a column. And, the code stops working.

I prefer to always use explicit joins.