Solved

Natural Join in SQL

Posted on 2011-02-15
5
1,764 Views
Last Modified: 2012-05-11
In SQL, how does natural join work?
0
Comment
Question by:JCW2
5 Comments
 
LVL 10

Accepted Solution

by:
himanshut earned 250 total points
ID: 34902803
0
 

Author Comment

by:JCW2
ID: 34909329
Can you explain natural join and inner join on this page?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 34909636
copy from the wiki page:


Natural join
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.
Most experts agree that NATURAL JOINs are dangerous and should never be used.


when you look at the example(s) on that page, you can see it documented.
in short, compared to a normal join where you have to specify which columns have to be used to "join", the natural join will implicitly join on columns with the same name... which is very bad idea, as at some point, you might add another column (with the same name) to either table modifying the query's behavior dramatically.
0
 

Author Closing Comment

by:JCW2
ID: 34910530
Thank you for your help.
0
 
LVL 22

Expert Comment

by:dportas
ID: 35749999
Standard SQL does have a join operator called NATURAL JOIN. Eg: SELECT * FROM tbl1 NATURAL JOIN tbl2;
However:

- It is not really the same as the relational model operator called natural join and usually written as |X|
- The SQL version of natural join is not widely used and many people consider it poor practice to use it at all.
- Many SQL DBMSs don't even support the NATURAL JOIN operator (Oracle does. MS SQL Server doesn't).
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now