Trainers discussing SQL Server, Oracle and MySQL

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:

  1. 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.
  2. 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?

No TweetBacks yet. (Be the first to Tweet this post)

3 Responses to “Why SQL Server Doesn’t Support Natural Join Syntax”

  1. [...] 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 [...]

  2. 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 !

  3. 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.

Tweetbacks

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

© Webucator, Inc. All rights reserved. | Toll Free: 877-932-8228 | UK: 0808-101-3484 | From outside the USA: 315-849-2724 | Fax: 315-849-2723