CODEWITHSUNDEEP
sqlnatural-join
user2162278
user2162278

Reputation: 67

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

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?

Upvotes: 2

Views: 11585

Answers (3)

Anubhav Shrimal
Anubhav Shrimal

Reputation: 111

Let us assume that Customer and Addresses schema is as follows:

Customer(ID_customer, name)
Addresses(ID, address)

then natural join query would be as follows:

SELECT * FROM (Customer AS Customer(ID, name) NATURAL JOIN Addresses);

This will perform the natural join as intended joining the ID attributes of both the tables.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270181

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.

Upvotes: 7

Mike Gardner
Mike Gardner

Reputation: 6651

SELECT whatever FROM Customers C, Addresses A
WHERE C.ID_Customer = A.ID

Upvotes: 1

Related Questions