1

When using SQL to access 2 tables, I usually do something like this:

SELECT table1.id, table1.name, table2.value
FROM table1 JOIN table2 ON table1.table2_id = table2.id
WHERE table1.name LIKE 'abc%'

Using a join to connect the two tables.

However in a project I have taken over I keep encountering this approach:

SELECT table1.id, table1.name, table2.value
FROM table1 as t1, table2 as t2
WHERE t1.table2_id = t2.id AND table1.name LIKE 'abc%'

Where the tables are not joined, but connected together in the where statement.

In what situations is this approach better than joining the two tables?

Jimmery
  • 9,783
  • 25
  • 83
  • 157
  • 5
    No situations whatsoever. That syntax was overtaken decades ago when SQL introduced the `JOIN` keyword. – Gordon Linoff Jan 15 '20 at 11:33
  • 3
    30 years ago, comma joins were all the rage, but not since then. Execute `EXPLAIN EXTENDED [your query]` followed by `SHOW WARNINGS;` for both queries. Come back with any further questions you may have. – Strawberry Jan 15 '20 at 11:33
  • Heh. Seems like I have a lot of SQL code to rewrite/update - if anyone can add something along the lines of "no situation whatsoever" as an answer I will mark it as correct. – Jimmery Jan 15 '20 at 12:12

1 Answers1

1

The multiple select was the older way and JOINs are new way to do same thing. The syntax is different but the performance looks same. As the optimizer do multiple select replaced with JOIN syntax.

Below is reference you can find helpful: Multiple Table Select vs. JOIN (performance)

Build Succeeded
  • 1,153
  • 1
  • 10
  • 24