Natural join with more than one common attribute in two tables [duplicate]

Question

I can understand how natural join works when the two tables have only one common attribute. What if they have two ones? Table 1 have 3 attributes: A, B, C Table 2 has 3 attribute: A, B, D

First two rows in table 1:

1 2 3
4 5 6

First two rows in table 2:

1 3 4
8 5 8

What is the result of a natural join between the two tables?

Ninetta

Natural Join is simply a short hand for an Inner Join that the Engine can automatically construct the join clause for from the table metadata. – Baroja

6993 views with only two answers and none of them accepted yet, come on somebody helps? – Knop

@PieterGeerkens no, NATURAL JOIN is a join type in its own right and is not as simple as you describe e.g. it eliminates the duplicate columns the nearest equivalent INNER JOIN is compelled to generate for legacy (pre-1992) purposes. – Conquian

Does this answer your question? Difference between natural join and inner join – Snigger

Answer 1

Natural Join is a variant of INNER JOIN where join condition is implicit on common column from both tables. In your case, the query in Natural Join can be written as below which will not return any result since it will try to match both A and B

select *
from table1
natural join table2

The same can be written in Inner Join like below

select t1.*
from table1 t1
inner join table2 t2
on t1.a = t2.a and t1.b = t2.b

See for yourself Fiddle Demo

Thorfinn

You Fiddle doesn't build for me but logically they cannot have the same result i.e. the first would include C and the second would not (aside: your code would fail for on a case-sensitive database e.g. A > a) ...but what is the point of translating it into an INNER JOIN anyhow?! – Conquian

Natural join is not a variant of inner join. They return the same result only when there are no columns in common. Natural join returns 1 copy of each common column & can be expressed via an inner join, coalesce & a select clause. – Snigger

Answer 2

In the case of your two records above, nothing will be matched. It will look for the case when A & B in the left table match A & B in the right table.

Olivette

@pln nice one. I've not seen SQL fiddle before. – Olivette

Answer 3

Actually Natural Join is something (Cross product + some condition)

The short form of natural join is:

Select * from Table_A NATURAL JOIN Table_B

So, an alternative way of writing this would be:

Select * from Table_A , Table_B where (Table_A.id = Table_B .id)

This is equivalent to Natural Join

(Table_A , Table_B) symbolises cross product

(Table_A.id = Table_B .id) symbolises common condition

Katti

Inner join returns only one copy of each common column. Also, join expressions have meaning inside a from clause without any reference to a select clause. Also this is very poorly worded & does not explain natural join & the example is not from the question & the examples aren't related. Etc. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. However, how natural join works is a duplicate many times; please don't answer duplicate questions, flag/vote them as duplicate. – Snigger

Answer 4

I will try to explain the difference between NATURAL JOIN and INNER JOIN on the basis of use-case.

We have two tables T1(A, B, C, D) & T2(B, C, X, Y), where the alphabets (A, B, C, D, X, Y) represent fields/attributes.

CASE 1: say I have to pull all rows which have a common entry in 'C' across both tables.

query: SELECT * FROM T1 INNER JOIN T2 ON T1.C=T2.C;

EXPLAINATION ON WHY NATURAL JOIN WILL NOT WORK IN THE ABOVE CASE --

say we use NATURAL JOIN.
SELECT * FROM T1 NATURAL JOIN T2;
We know that, T1 & T2 have two similar attributes/fields ('B' and 'C') so, NATURAL JOIN will look for all such entries where (T1.B=T2.B) AND (T1.C=T2.C)
Only the rows which satisfy the above condition, will be included in the result set.
Whereas, we only need the rows to have a common entry for field 'C' for our purpose.

Raeannraeburn

If anyone is looking for an answer curated to their doubts on inner join and natural join, please leave a comment here! I am also looking for improving my answer in the process :) – Raeannraeburn

Answer 5

I am studying for the certification and I got this doubt also. NATURAL JOIN ALWAYS compare the similar columns in the tables. If you have one pair of similar columns, it will compare and it'll show the matches. Chances are higher. Now, if you have 2 pairs, it will compare and it'll bring less results. the content has to be equal. Create a table yourself and do the test

Imamate