SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NATURAL JOIN


NATURAL JOIN

Author
Message
dglane
dglane
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 129
I have never seen the specific syntax "NATURAL JOIN" used (or documented) in T-SQL. Maybe it is. However, I am having a heck of a time proving it one way or the other. Sure, I can look at the online books docs and I cannot find "NATURAL JOIN" there anywhere but that does not mean it is not allowable.

I tried googling too but I really need positive confirmation on its validity/invalidity.

Please help.

Thanks.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97575 Visits: 38988
Never heard of a natural join. Could you provide more context in which it is used? That might help.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Matt Miller (4)
Matt Miller (4)
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30273 Visits: 19009
Actually - if I remember correctly, a "natural join" is what you get when using some of the newer join commands like INTERSECT or EXCEPT. There isn't a specific join set specified: instead, the two queries are joined by matching each of the columns up based on their order within the query (so 1st column of 1st query matches up to 1st col of 2nd query, then match up the 2 column #2, then match up 3, etc....)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Ronald H
Ronald H
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1676 Visits: 630
I don't think a natural join specifically exists in TSQL... And the only thing I can think of what it could mean is a normal join between 2 tables with the same ID in the ON, something like:
FROM Orders O
INNER JOIN Clients C ON C.ClientID = O.ClientID


with what makes it natural that ClientID exists in both tables.

Ronald Hensbergen

Help us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/
-------------------------------------------------------------------------
2+2=5 for significant large values of 2
dglane
dglane
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 129
Well, I'll tell you the way it was used that was very painful for me. I just took a test on SQL that had statements such as...

SELECT *
FROM table1
NATURAL JOIN table2

(That is almost certainly not a true example from the test but "NATURAL JOIN" was explicitly part of the syntax used.)

I know there are some T-SQL pieces that I do not know or am not proficient at but I was certain I had never seen this syntax in books online, actual code, code examples, etc.

Anyway a good 1/4 or more had questions about NATURAL JOIN or natural joins in general so the test did not go well for me.

Books online specifically refers to three joins: INNER, OUTER and CROSS. There are a handful of references to a generic "natural join" in all of books online but I found no actual "NATURAL JOIN" syntax. Frankly I had never even heard the term -- and I hope I never hear it again.

I am fairly sure PL/SQL uses this syntax but I believe the test administrator did not make a mistake and give me a PL/SQL test.
dglane
dglane
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 129
The point of my original query, as you may have guessed, is to help me overcome the poor test score (assuming "NATURAL JOIN" is truly not supported syntax).
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14975 Visits: 11848
A NATURAL JOIN is an inner join where the RDBMS automatically selects the join columns based on common columns names. Some RDBMS vendors, like Oracle but not SQL Server, implement a NATURAL JOIN operator:
SELECT
*
FROM
dbo.Product
NATURAL JOIN
dbo.ProductInventory



For more info:
http://en.wikipedia.org/wiki/Join_(SQL)#Natural_join
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97575 Visits: 38988
Okay. That explains why I have never heard of a natural join. I've never had the opportunity to work with Oracle.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
dglane
dglane
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 129
Michael Valentine Jones (11/11/2008)
A NATURAL JOIN is an inner join where the RDBMS automatically selects the join columns based on common columns names. Some RDBMS vendors, like Oracle but not SQL Server, implement a NATURAL JOIN operator:
SELECT
*
FROM
dbo.Product
NATURAL JOIN
dbo.ProductInventory



For more info:
http://en.wikipedia.org/wiki/Join_(SQL)#Natural_join


I had found that article too but I would really love to find your comment (i.e. "Some RDBMS vendors, like Oracle but not SQL Server, implement a NATURAL JOIN operator" -- italics added by me) in that article or some other source that I could offer the test admin.
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35984 Visits: 9518
dglane (11/11/2008)
I had found that article too but I would really love to find your comment (i.e. "Some RDBMS vendors, like Oracle but not SQL Server, implement a NATURAL JOIN operator" -- italics added by me) in that article or some other source that I could offer the test admin.


You can use any SQL Server to prove this.

Just type in the following:

SELECT *
From sys.tables t1
INNER JOIN sys.tables t2
ON t1.object_id=t2.object_id;
GO

SELECT *
From sys.tables t1
NATURAL JOIN sys.tables t2;


Note that the first one works, but the second one gives a Syntax Error. Note also that although "INNER" is highlighted (grey on my machine), "NATURAL" is not, indicating that it is not recognized as a keyword.

Note also, that the Books Online article "Using Joins" lists all of the possible Join types: "NATURAL" is not listed there. Note also that the Books Online "FROM (Transact-SQL)" article in the Transact-SQL Reference manual, does not list "NATURAL" as a valid join type under "{joined_table}: ... {join_type}:" in the syntax diagram.

Note finally, that "if it ain't in the syntax diagram, then it ain't valid syntax".

(Language Reference manuals only ever list what is valid, they never list things that are "invalid", as that is infinite.)

If that does not work, then challenge you Test Admin to get any SQL command with "NATURAL JOIN" in it to work on any SQL Server.

The Admin gave you the wrong test.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search