2

What is the difference between:

SELECT * 
FROM table_name1, table_name2 
WHERE table_name1.t2_id = table_name2.id;

and

SELECT * 
FROM table_name1 
   JOIN table_name2 ON table_name1.t2_id = table_name2.id;

?

NOTE: t2_id is the foreign key of table_name1 that holds the value of the primary key (id) of table_name2.

I mean, these two different queries both return the same result. If they return same, then what's the point of using JOIN?

P.S.: Please consider that I'm beginner in SQL.

Curious Inside
  • 21
  • 2
  • 1
    Join notation is the ANSI 92 standard , notation is the ANSI 89 standards. Do you want to follow the newer standard or continue to propagate the old one? Additionally, the `,` notation is generally frowned upon when developing new SQL as it doesn't follow the standard set some 25 years ago. Many people indicate the join notation is easier to read/maintain for subsequent individuals; helps reduce the problems with forgetting join conditions rersulting in cross joins. Imagine joining 6 tables each with 3 values in key and having limits. Now maintain that where clause to remove one table. – xQbert Jun 19 '17 at 13:32
  • both are correct pls refer this https://stackoverflow.com/questions/20138355/whats-the-difference-between-comma-separated-joins-and-join-on-syntax-in-mysql There is no difference at all. First representation makes query more readable and it looks very clear as to which join corresponds to which condition. – Nithin Jun 19 '17 at 13:32
  • Thou doeth not useth "join". Ye canst writeth inn ye olde syntax. – Gordon Linoff Jun 19 '17 at 13:38
  • Thanks for the fast replies! I got it! And `JOIN` has a better performance, right? By the way, I just figured out that my question is a possible duplicate, thanks to @fancyPants! I should research more before posting the question. – Curious Inside Jun 19 '17 at 13:42
  • and consider: https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins but to answer the question. one has a `,` in the from. The other uses the keyword `join` and one has a `where` clause while the other has an `ON` clause. any good compiler will result in the same execution plan; thus same performance. This is one of those topics that has arguments on both sides. I prefer using the current standards. – xQbert Jun 19 '17 at 13:43
  • @GordonLinoff, your comment sounds like a holy book, yaddadamean? ;) – Curious Inside Jun 19 '17 at 13:45
  • @xQbert, appreciated! – Curious Inside Jun 19 '17 at 13:52

1 Answers1

-1

Generally JOIN considered more readable, especially when you join lots of tables. We can find out what are the same columns in two tables.A result of two tables with join returns filter with two columns match result.This can actually change the results of your query depending on how it is setup.

WHERE - is relational model between two tables.

Damini Suthar
  • 1,470
  • 2
  • 14
  • 43