2

I have two mySQL statements. First is:

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

The second is:

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

Both produce the same result, but second doesn't contain reference on Customers table in FROM request.

My question is - what is the difference between these two sql statements? In which cases should I use JOIN and in which cases should I use simple SELECT from two tables?

D-Shih
  • 44,943
  • 6
  • 31
  • 51
Nastro
  • 1,719
  • 7
  • 21
  • 40
  • Run EXPLAIN EXTENDED [YOUR QUERY], immediately followed by SHOW WARNINGS. Then you can answer your own question – Strawberry Nov 03 '18 at 13:01
  • I don't think you are using MySQL, so why you tag SQL Server and MySQL? – Ilyes Nov 03 '18 at 13:03
  • 2
    **[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins)** – Ilyes Nov 03 '18 at 13:08
  • 1
    The second version shows the explicit relationship between the tables used. It is a lot easier to understand. Especially when a lot of tables are used. Also it is easier for the database engine to work out how the database is being accessed. see [SQL left join vs multiple tables on FROM line?](https://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line) Note: The `join on` condition can be complex. You are not limited to simple rules. – Ryan Vincent Nov 03 '18 at 13:10
  • 1
    The first is proper SQL. The second harkens back to an earlier form of the language, as if you were asking questions in Shakespearean English. Use proper, explicit, **standard** `JOIN` syntax. – Gordon Linoff Nov 03 '18 at 13:19

2 Answers2

2

They are the same except the second is easier to read, so you should use that one.

LoztInSpace
  • 5,584
  • 1
  • 15
  • 27
  • Also the first is much easier to accidentally derive a Cartesian product (cross join) and forces you to mix join and filter criteria, which can be useful to separate when troubleshooting. [IMHO you shouldn't ever use `table1, table2` join syntax](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins). – Aaron Bertrand Nov 03 '18 at 13:05
0

Those JOIN are different, although the result are the same.

The First one is CROSS JOIN and adds the condition in where, which is implicit CROSS JOIN

The second one is INNER JOIN

If you want to connect two tables I would use INNER JOIN instead of CROSS JOIN Because the intention of the inner join table is clearer

D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • 1
    They're both inner joins, as EXPLAIN EXTENDED would prove. – Strawberry Nov 03 '18 at 13:24
  • imo, the useful syntax of 'cross join` is that it is clear to anyone that reads the code that you deliberately want all the possible combinations to be considered. That this is the default behavior of an 'inner join' can cause issues for newcomers when they get 'duplicate rows' in the output. – Ryan Vincent Jan 01 '19 at 15:24