Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

NATURAL JOIN Expand / Collapse
Author
Message
Posted Tuesday, November 11, 2008 3:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 24, 2012 3:25 AM
Points: 37, 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.
Post #600976
Posted Tuesday, November 11, 2008 3:46 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 20,753, Visits: 32,569
Never heard of a natural join. Could you provide more context in which it is used? That might help.




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)
Post #600984
Posted Tuesday, November 11, 2008 3:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 7,112, Visits: 15,493
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?
Post #600988
Posted Tuesday, November 11, 2008 3:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,123, Visits: 603
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
Post #600989
Posted Tuesday, November 11, 2008 4:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 24, 2012 3:25 AM
Points: 37, 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.



Post #600993
Posted Tuesday, November 11, 2008 4:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 24, 2012 3:25 AM
Points: 37, 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).
Post #600995
Posted Tuesday, November 11, 2008 4:09 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, October 25, 2014 3:18 AM
Points: 3,108, Visits: 11,504
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
Post #600996
Posted Tuesday, November 11, 2008 4:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 20,753, Visits: 32,569
Okay. That explains why I have never heard of a natural join. I've never had the opportunity to work with Oracle.




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)
Post #601009
Posted Tuesday, November 11, 2008 5:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 24, 2012 3:25 AM
Points: 37, 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.
Post #601018
Posted Tuesday, November 11, 2008 5:50 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
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."
Post #601028
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse