3

which example below is best practice for inner joins? the examples below are pretty simple but, what if there are multiple tables involved? which approach would you choose?

example queries:

simple

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

using keyword INNER JOIN

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
milesmiles55
  • 677
  • 2
  • 13
  • 19

3 Answers3

6

Since 1992 ANSI recommends the JOIN notation, and regarding to readability this is what I would recommend using.

Everything has been said on this topic, but I will just remind that from a logical point of view it makes much more sense to separate the join conditions from the value filters for 2 reasons:

  • that's not the same thing at all
  • Oracle internally (and mysql probably too, even though I don't really know) first calculates intermediate recordsets and THEN, afterwards, applies the WHERE filters.
Sebas
  • 21,192
  • 9
  • 55
  • 109
0

I'd recommend explicitly stating JOIN type and using ON clause. But you need to use it correctly. In your example you forgot to include FROM clause and suppliers table.

SELECT suppliers.supplier_id, 
       suppliers.supplier_name, 
       orders.order_date
  FROM suppliers s INNER JOIN 
       orders o ON s.supplier_id = o.supplier_id;
peterm
  • 91,357
  • 15
  • 148
  • 157
  • 1
    Why are you typing `inner join` instead of `join`? Are your work is priced based on code size? – Egor Skriptunoff Apr 10 '13 at 13:55
  • @EgorSkriptunoff For the sake of readability. Do these five characters make a huge difference for you? – peterm Apr 10 '13 at 14:01
  • http://stackoverflow.com/a/15892024/1291428 – Sebas Apr 10 '13 at 14:12
  • IMHO, it is more easily to read/understand more shorter version of text. You are getting nothing concerning readability by prepending `inner` keyword. – Egor Skriptunoff Apr 10 '13 at 14:18
  • It depends. It's like an emphase, INNER really insist visually on the fact that the result will be an intersection. But I agree with you, this is mostly a habbit thing and I myself have problems going ahead and leaving it behind. Please note that some (old and outdated) RDBMS don't accept the JOIN key word by itself. – Sebas Apr 10 '13 at 14:21
  • 1
    @EgorSkriptunoff It's subjective and a matter of preference. And one can always say that you win almost nothing not specifying it. – peterm Apr 10 '13 at 14:24
0

In terms of functionalities both WHERE AND JOINS are same..

user1102001
  • 689
  • 2
  • 10
  • 21
  • 2
    To be clear, both forms are joins, but one has the _join condition(s)_ in a WHERE clause while the other has the join condition(s) in an explicit ON clause. – Wiseguy Apr 10 '13 at 14:09