What will be result of Natural Join.

[Mahtab Alam]

I have a table tab1 with three cloumns id,name,age
And I have another table tab2 with two columns id,name

Now If I try to do natural join ,Which column will natural join will use or it will give error .
It does not give error , It just says No rows selected.

[Ulf Dittmer]

Should there be matching rows? Can you post a minimal set of data for both tables that does not behave in the way you expected?

[Mahtab Alam]

In tab1
ID NAME AGE
-------------------------------------
111 A 21
222 B 22
333 C 23

In tab2
ID NAME
-----------------------
111 A
444 D
555 E


select id,name,age
from tab1 natural join tab2;

So if it does natural join using ID it should have given me
ID NAME AGE
-------------------------
111 A 21
Even if it does natural join using NAME it should have given me
ID NAME AGE
---------------------------
111 A 21

But it says No rows selected

[Martin Vashko]

Natural join joins on all columns that appear in both tables at the same time. In your case it joins on both NAME and ID. Therefore you're doing something different from what you're showing us - with the same data you've shown us, I do get a row back:
SQL> create table t1(id number, name varchar2(10), age number); Table created. SQL> create table t2(id number, name varchar2(10)); Table created. SQL> insert into t1 values(111, 'A', 21); 1 row created. SQL> insert into t1 values(222, 'B', 22); 1 row created. SQL> insert into t1 values(333, 'C', 23); 1 row created. SQL> insert into t2 values(111, 'A'); 1 row created. SQL> insert into t2 values(444, 'D'); 1 row created. SQL> insert into t2 values(555, 'E'); 1 row created. SQL> select id, name, age from t1 natural join t2; ID NAME AGE ---------- ---------- ---------- 111 A 21
Perhaps there is another column in both of your tables which you haven't shown us?

[Mahtab Alam]

You are right Martin.
I saw entries in both tables none of them have same values for id and name.
Sorry for that mistake.

[Ulf Dittmer]

A good example of AvoidRedHerrings.

[Mahtab Alam]

Ulf it was bad one.
I will remember that term for sure.
And It was not intentional .
Before posting to this forum ,I first try it by myself.If I am not getting it Then I come here looking for Martin.
Next time I will make sure

[Martin Vashko]

It would actually help a lot if you prepared your examples in the form of SQL scripts that others could run on their systems (yes, a SSCCE ), as I've already mentioned several times. That way we'd know for sure that we're all looking at the same code/data. You would benefit too - you could re-run your tests easily and make sure that you're getting consistent results.

Note: I'm usually posting outputs from the entire SQL scripts, so that it shows the commands and their output at the same time and is easy to follow. This can be easily turned into a script you can run on your own, using some filtering and find/replace. Just for reference, my original SQL script for this one was:drop table t1; drop table t2; set echo on spool a.log create table t1(id number, name varchar2(10), age number); create table t2(id number, name varchar2(10)); insert into t1 values(111, 'A', 21); insert into t1 values(222, 'B', 22); insert into t1 values(333, 'C', 23); insert into t2 values(111, 'A'); insert into t2 values(444, 'D'); insert into t2 values(555, 'E'); select id, name, age from t1 natural join t2; spool off exit