educative.io

Natural join with multiple columns of the same name

Courses
,
#1

Let’s say we have two tables with three columns of the same name. Then Natural join will apply join on all these columns and will it use AND operator to combine conditions?

  1. Actors (FirstName,LastName,CityId,CityName)
  2. City(CityId,CityName,ProvinceId)

Possible Query with Natural join:
select FirstName,LastName,CityId,CityName from Actors
Natural join City

What will be expected translation of it
select a1.FirstName, a1.LastName,a1.CityId, a1.CityName from Actors as a1
inner join City as c1
on c1.CityId=a1.CityId and c.CityName=a1.CityName

Course: https://www.educative.io/collection/5352985413550080/6335716377231360
Lesson: https://www.educative.io/collection/page/5352985413550080/6335716377231360/6529091323822080

#2

Hi @ahmed_anwar

My name is Shahrukh Naeem. I hope everything is going well with you. Thank you for reaching out about this. I will try my best to answer your query!

Table1 NATURAL JOIN Table2

is equivalent to

Table1 JOIN Table2 ON Table1.Col1 = Table2.Col1 AND Table1.Col2 = Table2.Col2 
... Table1 NATURAL JOIN Table2

with all column names which exist in both tables being added to the ON clause. So yes, since it is an AND not an OR which is implied, they must all match. This is useful if you have a 2-part Key, e.g. company_code + username, and a hazard if you have standard named non-key fields, such as date_created

I hope that this guide is helpful. Remember that I am always available via message to help you with any difficulty you might encounter.

Regards,

Happy Learning :slight_smile:

2 Likes