2

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?

Mat
  • 202,337
  • 40
  • 393
  • 406
user2162278
  • 67
  • 1
  • 2
  • 10
  • What is "the program"? – D'Arcy Rittich May 02 '13 at 13:28
  • 1
    A natural join is an equi-join based on _columns with the same name_. If the columns don't have the same name, you can't have a natural join, by definition. Use a normal join. (And don't ever write "urgent" in your questions, especially not in the title, unless you're looking for negative attention.) – Mat May 02 '13 at 13:30
  • @Mat Natural join of two tables with no columns in common is defined & is cross join. The asker says it "won't work correctly" but what they mean is "won't give the answer I want". – philipxy Nov 07 '18 at 05:59

3 Answers3

6

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This did the job for me, I had two tables Cities(code, name, state) and States (code, state) and I could match Cities(state) with States(code) perfectly – Cesar Pintos Jul 27 '21 at 15:05
2

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.

Anubhav Shrimal
  • 91
  • 3
1
SELECT whatever FROM Customers C, Addresses A
WHERE C.ID_Customer = A.ID
Mike Gardner
  • 6,611
  • 5
  • 24
  • 34