1

I see that this example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

produces the same output as this example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders, Customers
WHERE Orders.CustomerID=Customers.CustomerID;

Is there any advantage in the INNER JOIN command?

The Student
  • 27,520
  • 68
  • 161
  • 264
  • "Is there any advantage in the INNER JOIN command?", NO. One is using ANSI-92 SQL compliance the other method predates the ANSI standard. There really is no difference if the RDBMS engine supports both standards; the compiler ***should*** generate the same execution plan. I say **should*** because in some cases outer joins can execute with different plans. I've not found that the case with INNER joins and newer RDBMS engines. – xQbert Aug 17 '15 at 20:27
  • 2
    http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause I think that may have the answer to your question. Thanks. – maxshuty Aug 17 '15 at 20:27

2 Answers2

3

Your former example is standard ANSI/ISO SQL. Your latter example is deprecated SQL. With the former syntax (ANSI/ISO) , the join criteria are clearly delineated; with the latter (deprecated) syntax, the join criteria are intermixed with the where clause filters making things much more difficult to sort out.

It doesn't matter so much for simply queries like yours, but when you have to deal with much more complicated queries (try 12 or 15 tables with a mixture of left, right and inner joins!), you will understand why the old syntax is deprecated.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
1

INNER JOIN is for clarity. Using WHERE as in your second example can get lost in more complex queries, making it difficult to determine how your tables are joined and make it more difficult to troubleshoot problems.

Russ
  • 4,091
  • 21
  • 32