12

When joining tables one can traditionally use the SQL89 way of joining like

SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id;

But since the SQL92 standard, we can now join using the JOIN syntax

SELECT * FROM t1 JOIN t2 on t1.id=t2.t1_id;

Is there any reason why someone would SELECT from multiple tables without joining? Now, I know people concatenate data using UNION, but that is not what I'm talking about.

Is the reason we add tables with commas in the FROM clause strictly for backwards compatibility? Or are there any realistic scenarios which using the old syntax would be impossible by doing just joins?

Kirk Backus
  • 4,776
  • 4
  • 32
  • 52
  • 1
    Why would we use multiplications while we can use cycles of sums? anyway, +1 – STT LCU Jul 12 '13 at 15:03
  • Possible duplicate of [Why isn't SQL ANSI-92 standard better adopted over ANSI-89?](http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89) – LittleBobbyTables - Au Revoir Jul 12 '13 at 15:06
  • `Is there any reason why someone would SELECT from multiple tables without joining?` Maybe they want a Cartesian product? – LittleBobbyTables - Au Revoir Jul 12 '13 at 15:06
  • There are no realistic circumstances where you would using the old syntax with a `,`. The new syntax is an improvement in every respect that I can think of (readability, maintainability, expressability for outer joins). But, the old syntax continues to be supported in the standard to be backward compatible. – Gordon Linoff Jul 12 '13 at 15:08
  • I have never seen a SQL statement in the old syntax that could not be expressed more cleanly in the ANSI-92 syntax. I find it makes debugging so much simpler to separate join conditions from predicates. – WoMo Jul 12 '13 at 15:08
  • related question. Is both version (e.g. when using old syntax for join) are equivalent performance-wise? – eran Jul 12 '13 at 15:13
  • I'm sure there is/was actual justification when the standard was written, but in practice I find the SQL92 convention to be clearer. It keeps the join predicates out of the WHERE clause, makes it easier to string together multiple joins, and I don't remember off the top of my head how you do an OUTER JOIN implicitly. The explicit notation makes all of that more straightforward. – Peter Tirrell Jul 12 '13 at 15:13
  • You are joining, you are just joining implicitly. That said, it is a poor practice to use implicit joins because they are far easier to get wrong and harder to maintain (you shouldn't mix implicit joins and explict ones for instance and you need the explicit ones in some databases if you are doing left joins). Especially bad is when the old code has an implict cross join because the maintainer has no idea if it was a mistake (the most common scenario) or if the cross join is actually neeeded. – HLGEM Jul 12 '13 at 15:16

2 Answers2

7

If you need a full cartesian product for some reason it could be useful. For example if you had a car model table and a car color table and you wanted to enumerate all of the options you wouldn't need a join. That being said, I would consider this an edge case.

munch1324
  • 1,148
  • 5
  • 10
  • 1
    I think that can be accomplished with a `FULL OUTER JOIN` – Kirk Backus Jul 12 '13 at 15:07
  • I think you are right, I was just thinking of any possible use of 'FROM table1, table2' syntax. I think an outer or cross join would be more legible as well – munch1324 Jul 12 '13 at 15:12
  • Hi @KirkBackus - FYI `FULL OUTER JOIN !=` the cartesian product. [Further reading](https://stackoverflow.com/a/27857150/185123) – spottedmahn Dec 06 '18 at 18:02
4

I'll admit that muscle memory kicks in and sometimes I use the 89 way before thinking. But I do prefer the 92 method because it keeps the WHERE clause cleaner. The only conditions in the WHERE clause are the ones relevant to your business case while the criteria that exist simply to define relationships are neatly in JOIN clauses.

dazedandconfused
  • 3,131
  • 1
  • 18
  • 29