1

The following queries both select data from a posts table and a users table. The first query uses a join the second doesn't... My question is why would you use a JOIN?

Query with JOIN:

SELECT u.*, p.* FROM users AS u
JOIN posts AS p ON p.user_id=u.user_id
WHERE u.user_id=1

Query without:

SELECT u.*, p.* FROM users AS u, posts AS p
WHERE p.user_id=u.user_id
AND u.user_id=1
Mureinik
  • 297,002
  • 52
  • 306
  • 350
jon
  • 1,429
  • 1
  • 23
  • 40
  • 2
    `JOIN` is the correct syntax. Commas in the `FROM` clause are archaic. We don't use them for pretty much the same reason we don't typically use words like "thou" and "doth" in our speech. – Gordon Linoff Jan 17 '16 at 17:55
  • 2
    The 2nd type of query is used in stone age and called implicit join and the first one is called explicit join and used in current days. – Abhik Chakraborty Jan 17 '16 at 17:55
  • 1
    @GordonLinoff Thou doth explained that nicely. – Jon Jan 17 '16 at 17:57
  • 1
    @AbhikChakraborty: It's 24 years since SQL-92, so explicit JOIN-syntax is Bronze Age :-) – dnoeth Jan 17 '16 at 18:37
  • LOL @dnoeth I calculated as 80's when I was born was cold age, 90's stone age and so on here in India :) – Abhik Chakraborty Jan 17 '16 at 18:40

3 Answers3

3

The second form is called an implicit join. First and foremost, implicit joins are considered deprecated by most s. Personally, I sincerely doubt that any major RDBMS will drop support for them any time in the near future, but why take the risk?

Second, explicit joins have a standard way to perform outer joins. Implicit joins have all sorts of unreadable hacks solutions (like, e.g., Oracle's (+) syntax), but, as far as I know, nothing standard that has a reasonable expectancy of portability.

And third, and I admit this is purely a matter of taste, they just look better. Using explicit joins allows you to logically separate the conditions in the query to the "scaffolding" needed to join all the tables together and the actual logical conditions of the where clause. With implicit joins, everything just gets lumped into the where clause and with as little as three or four tables it becomes pretty hard to manage.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
2

The second query is using a join. That's what the comma means in users AS u, posts AS p. This is an implicit join (implicit because although you're not explicitly using the JOIN keyword, you're getting its effects) also known as a CROSS JOIN, and means "every row of the left table, joined with every row in the right table".

The use of JOIN ... ON syntax is (in my opinion) much more explicit and readable, due in no small part to moving the joining condition from the WHERE clause to being directly attached to the JOIN, and also opens up the syntax for other join types (LEFT JOIN, the default, and INNER JOIN) with different semantics.

jbafford
  • 5,528
  • 1
  • 24
  • 37
2

It is purely a choice of style. These two statements will be interpreted identically by the server.

When you use a comma (,) in the FROM clause, this is implicitly using a CROSS JOIN.

If you have lots of conditions in the WHERE clause, it may more clear to distinguish between conditions intended to connect tables (tableA.ID = tableB.tableA_id) versus conditions intended to filter. You can achieve this by putting the connection conditions next to an explicit JOIN.

William Entriken
  • 37,208
  • 23
  • 149
  • 195