0

Example 1:

SELECT name 
FROM Customer, Order 
WHERE Customer.id = Order.cid

Example 2:

SELECT name 
FROM Customer JOIN Order 
ON Customer.id = Order.cid

What is the difference between these two queries? When should I cross two tables vs JOIN?

David Foster
  • 53
  • 1
  • 8

2 Answers2

5

Both will give you identical result. So there is no real situation to use one over another.

The comma separated join, is an ANSI 89 standard join, INNER JOIN is the newer ANSI 92 standard join.

However comma separated join syntax is depreciated we always prefer to use INNER JOIN syntax. When you want to join more than one table it will be difficult to follow the join conditions in Where clause where as INNER JOIN syntax is more readable

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
1

CROSS JOIN operation is a Cartesian product. Result of CROSS JOIN operation between set A and set B is the superset that contains all values. Then with operator WHERE you are filtering this result set.

INNER JOIN operation will try to find rows from both tables that correspond predicate after keyword ON. That rows will go to the result set.

Practically, SQL engine can choose its own physical implementation CROSS JOIN operator. SQL engine doesn't have to get huge result set and then filter it. SQL engine behaviour will be similar when using INNER JOIN operation.

fabulaspb
  • 1,238
  • 8
  • 9