aspose file tools*
The moose likes Oracle/OAS and the fly likes What will be result of Natural Join. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "What will be result of Natural Join." Watch "What will be result of Natural Join." New topic
Author

What will be result of Natural Join.

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 244

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.


Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41509
    
  53
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?


Ping & DNS - my free Android networking tools app
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 244

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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:

Perhaps there is another column in both of your tables which you haven't shown us?
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 244

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
Marshal

Joined: Mar 22, 2005
Posts: 41509
    
  53
A good example of AvoidRedHerrings.
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 244

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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:
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: What will be result of Natural Join.