November 11, 2008 at 3:27 pm
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.
November 11, 2008 at 3:46 pm
Never heard of a natural join. Could you provide more context in which it is used? That might help.
November 11, 2008 at 3:56 pm
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?
November 11, 2008 at 3:58 pm
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 HensbergenHelp 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
November 11, 2008 at 4:04 pm
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.
November 11, 2008 at 4:07 pm
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).
November 11, 2008 at 4:09 pm
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:
November 11, 2008 at 4:48 pm
Okay. That explains why I have never heard of a natural join. I've never had the opportunity to work with Oracle.
November 11, 2008 at 5:17 pm
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:
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.
November 11, 2008 at 5:50 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 11, 2008 at 6:29 pm
dglane (11/11/2008)
I believe the test administrator did not make a mistake and give me a PL/SQL test.
The test administrator is human and evidently made a mistake.
There is no such thing as a NATURAL JOIN in T-SQL and the best proof is, as Barry pointed out, that a query with NATURAL JOIN won't run in SQL Server.
November 12, 2008 at 5:09 am
I work with Oracle daily. In Oracle, NATURAL JOIN will join two tables by all columns that have the same name and data type (actually it is really "compatible" data types, but let's not get into that).
It is not ANSI standard. I do not know if any other vendors allow the syntax, but it is usually considered a really bad thing to use.
I would say any test that focuses on them is coming from a company that has pretty horrible PL/SQL practices. I would think the best use of a question regarding them is to try to find people that don't know what they are because nobody should ever want to actually use them.
May 23, 2009 at 4:38 pm
IN ARTICLE "Joins in SQL Server 2005" (http://www.c-sharpcorner.com/UploadFile/raj1979/SqlJoins10012008164642PM/SqlJoins.aspx) THE AUTHOR TALKS ABOUT USING THE NATURAL JOIN SYNTAX IN T-SQL FOR SQL SERVER 2005.
I AM UNABLE TO USE THE "NATURAL JOIN" FEATURE IN MY T-SQL CODE.
THIS FEATURE DOES NOT SEEM TO BE DOCUMENTED IN THE BOOKS ONLINE.
EXAMPLES:
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;
GO
NOTE THAT THE FIRST ONE WORKS, BUT THE SECOND ONE GIVES A SYNTAX ERROR.
May 23, 2009 at 6:40 pm
Heh... curiously enough, Microsoft and Oracle have two different descriptions of what a "natural" join is.
http://support.microsoft.com/kb/136699
[font="Arial Black"]The Equi-Join and Natural Join[/font]
Almost all joins, including all examples given so far, are equi-joins and natural joins. The meanings of these terms are of little significance to the average user of Microsoft Query, but the next two paragraphs attempt to explain the terms for those who may be curious.
The equi-join is a join in which records are retrieved based on whether the join fields have matching values in both tables. That may seem like just a definition of a join, but it isn't. An example of a non-equal join is a join where records in the first table are joined to those records in the second table where the joined field in the first table is greater than (instead of equal to) the joined field in the second table (or less than, or anything besides equal to). Naturally, this returns more records than an equi-join.
A natural join is one in which only one of the two tables' joined fields is returned. Since these two fields are by definition identical in an equi-join, it is redundant to include both. For a non-equal join, it is important to include both of those fields. So, equi-joins and natural joins go together. You would want an equi- joins (which describes most joins) to be natural join by returning only one of the joined fields; but, if you ever use a non-equal join, you might also want to make it a non-natural join by returning both of the joined fields.
Natural Joins are, in fact, specified in the Ansi standards...
[font="Arial Black"]Format[/font]
{joined table} ::=
{cross join}
| {qualified join}
| [font="Arial Black"]{natural join}[/font]
{cross join} ::=
{table reference} CROSS JOIN {table factor}
{qualified join} ::=
{table reference} [ {join type} ] JOIN {table reference} {join specification}
[font="Arial Black"]{natural join} ::=
{table reference} NATURAL [ {join type} ] JOIN {table factor}[/font]
{join specification} ::=
{join condition}
| {named columns join}
{join condition} ::= ON {search condition}
{named columns join} ::= USING {left paren} {join column list} {right paren}
{join type} ::=
INNER
| {outer join type} [ OUTER ]
{outer join type} ::=
LEFT
| RIGHT
| FULL
{join column list} ::= {column name list}
The Ansi standards continue on the subject...
7) If NATURAL is specified or if a immediately containing a is
specified, then:
a) If NATURAL is specified, then let common column name be a that is equivalent to the
of exactly one field of RT1 and the of exactly one field of RT2. RT1 shall
not have any duplicate common column names and RT2 shall not have any duplicate common column
names. Let corresponding join columns refer to all fields of RT1 and RT2 that have common column
names, if any.
b) If a is specified, then every in the shall be
equivalent to the of exactly one field of RT1 and the of exactly one field
of RT2. Let common column name be the name of such a column. Let corresponding join columns refer
to the columns identified in the .
c) Let C1 and C2 be a pair of corresponding join columns of RT1 and RT2, respectively. C1 and C2 shall
be comparable. C1 and C2 are operands of an equality operation, and the Syntax Rules of Subclause 9.9,
“Equality operations”, apply.
d) If there is at least one corresponding join column, then let SLCC be a of s
of the form
COALESCE ( TA.C, TB.C ) AS C
for every column C that is a corresponding join column, taken in order of their ordinal positions in
RT1.
e) If RT1 contains at least one field that is not a corresponding join column, then let SLT1 be a of s of the form
TA.C
for every field C of RT1 that is not a corresponding join column, taken in order of their ordinal positions
in RT1.
ISO/IEC 9075-2:2003 (E)
7.7
©ISO/IEC 2003 – All rights reserved Query expressions 313
f) If RT2 contains at least one field that is not a corresponding join column, then let SLT2 be a of s of the form
TB.C
for every field C of RT2 that is not a corresponding join column, taken in order of their ordinal positions
in RT2.
g) Let the SL be defined as
Case:
i) If all of the fields of RT1 and RT2 are corresponding join columns, then let SL be “SLCC”.
ii) If RT1 contains no corresponding join columns and RT2 contains no corresponding join columns,
then let SL be “SLT1, SLT2”.
iii) If RT1 contains no fields other than corresponding join columns, then let SL be “SLCC, SLT2”.
iv) If RT2 contains no fields other than corresponding join columns, then let SL be “SLCC, SLT1”.
v) Otherwise, let SL be “SLCC, SLT1, SLT2”.
The descriptors of the columns of the result of the , with the possible exception of the
nullability characteristics of the columns, are the same as the descriptors of the columns of the result
of
SELECT SL FROM TR1, TR2
And, in article after article, folks recommend NEVER using them. I tend to agree unless you follow the Microsoft Definition which isn't what most folks think of for a Natural Join.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2009 at 7:53 pm
Jeff Moden (5/23/2009)
Heh... curiously enough, Microsoft and Oracle have two different descriptions of what a "natural" join is.http://support.microsoft.com/kb/136699
[font="Arial Black"]The Equi-Join and Natural Join[/font]
...
A natural join is one in which only one of the two tables' joined fields is returned. ...
Classic Microsoft: If they don't do it, they just redefine the term to mean something else that they do do.
Natural Joins are, in fact, specified in the Ansi standards...
[font="Arial Black"]Format[/font]
{joined table} ::=
{cross join}
| {qualified join}
| [font="Arial Black"]{natural join}[/font]
...
The Ansi standards continue on the subject...
7) If NATURAL is specified or if a immediately containing a <named ...
Say what!!! You're quoting ANSI standards??? ...
Alright buddy, Who are you and what have you done with Jeff!
😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply