On the face of it, this seems like a great feature and it can be very nice for writing quick queries.
Other major databases, such as MySQL and Oracle, do support natural joins. However, natural joins have some downsides:
- Because natural joins are implicit, there is no way to see what columns will be used in the join. You might not get what you think you’re getting.
- If a column name or type is altered or the column is removed from one of the tables, the next time the SELECT statement is run the join will break.
Microsft’s T-SQL statement does not support the use of the NATURAL JOIN clause, instead Microsoft considers the INNER JOIN clause that joins just 2 tables together having the same field name to be the same as a NATURAL JOIN.
An example:
SELECT c.CustomerID, S.TotalAmount FROM Customers c INNER JOIN Sales s ON c.CustomerID = S.CustomerID
Since the CustomerID name in both the Sales and Customer tables are identical, Microsoft considers this to be equalevant to a NATURAL JOIN.
For more information go to: Description of Joins in Microsoft Query
Natural joins are covered in Webucator’s Advanced Oracle SQL class and in our MySQL Development class, but not in our SQL Server classes as, as I’ve discussed here, SQL Server doesn’t support natural joins. Do you wish it did?
[...] JOIN is typically applied in one-to-one scenarios. But natural join is not supported by either SQL Server or LINQ to SQL. Natural join should be implemented via INNER [...]
April 14th, 2010 at 2:03 pm
Just a note, regarding point 2), the statement would also break if the join was explicit (in the case where one of the column used in the join is removed from the db or the data type changed).
but i totally understand reason 1).
thanks !
February 9th, 2011 at 10:05 am
Please learn something about the relational model (as developed over 50 years ago!) before trying to speak authoritatively about these things. There is far too much misinformation about SQL and this kind of article doesn’t help.
The natural join not a convenience feature. It is the only true relational join, and if used correctly, not only do those “downsides” become completely irrelevant, but in fact the strict use of natural join helps enforce good practices and avoid surprises.
April 25th, 2011 at 5:37 pm