Natural full outer join does not work in my SQL Server Management Studio

Question

I am working with Microsoft SQL Server 2012. I have two tables T1 and T2 and they both have an ID column.

When I enter the following SQL statement in my Microsoft SQL Server Management Studio

select ID 
from T1 
natural full outer join T2

it generated the following error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'T2'.

However, Microsoft SQL Server Management Studio accepts the following statement which I think is an equivalent statement:

select ID 
from T1 
full outer join T2 ON t1.ID = T2.ID

Does SQL Server Management Studio not support natural full outer join?

There are no natural joins in Sql server you have to explicitly tell sql server what join you want to do by using key words like RIGHT, LEFT or FULL joins — M.Ali, Feb 09 '14 at 21:24
Did you check the extensive, freely available [SQL Server Books Online documentation first?](http://technet.microsoft.com/en-us/library/bb510741.aspx) — marc_s, Feb 09 '14 at 21:49
It's not "SQL Server Management Studio" which rejects or accepts a query, it's SQL Server (the backend) — , Feb 09 '14 at 22:45

score 1 · Answer 1 · answered Feb 09 '14 at 21:23

1

SQL Server/Transact SQL simply doesn't support Natural Join syntax.

Btw, the parser is not complaining about the "natural" because it assumes this is a table alias :-)

answered Feb 09 '14 at 21:23

dnoeth

I guess I have a couple follow up questions. Is "select ID from T1 full outer join T2 ON t1.ID = T2.ID" the same as using natural join? Also, how do I write the equivalent statement without using outer join? Thank you – Lex L Feb 09 '14 at 21:26
full outer join and natural join are different by definition. It depends on what you are trying to do. As you might already know natural join is kind of equi join but only columns from one of the tables ( if same column name exists) is returned. – bhupendra patel Feb 09 '14 at 21:36

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

Doesn't look like there is support for Natural Joins yet look at the forum Microsoft Connect and Stack Overflow

edited May 23 '17 at 12:20

Community

answered Feb 09 '14 at 21:27

bhupendra patel

score 0 · Accepted Answer · edited May 23 '17 at 11:56

0

There are no Natural JOINS in Sql server, You have to explicitly tell sql server on which fields you want table to join.

1) Using ON clause and explicitly using the column names from both tables.

2) Or in older JOIN Syntax explicitly using the column names from both tables in WHERE Clause.

for another nice answer on this topic Read here

edited May 23 '17 at 11:56

Community

answered Feb 09 '14 at 21:30

M.Ali

I did a couple tests on the queries, and I think select * from t1 left outer join t2 ON t1.ID = t2.ID is equivalent to select * from t1, t2 WHERE t1.ID = t2.ID Could you confirm if that's true? thanks – Lex L Feb 09 '14 at 22:18
Natural join means that if there are two columns with the same name in two tables them tables will be joined using them columns. There is no guarantee that which columns will be used to join to or more table. very unreliable and very unexpected results. Now what is Natural join equivalent in sql server, there isnt any, sql server doesnt join two tables unless its told on which fields to join these tables and also you have to explicitly use LEFT, RIGHT or FULL join key words to get the results. – M.Ali Feb 09 '14 at 22:22
One more thing in sql server if you do not use any key word like LEFT RIGHT or INNER , by default Sql server does an INNER JOIN, but for any other type of join you have to explicitly use the KEY word RIGHT, LEFT or FULL. – M.Ali Feb 09 '14 at 22:25

3 Answers3