8

what is the difference between using comma or join between two different tables.

such as these two codes:

SELECT studentId, tutorId FROM student, tutor;


SELECT studentId, tutorId FROM student JOIN tutor;
user2749139
  • 109
  • 1
  • 3
  • 10
  • "Comma joins" like these are are old code and not guaranteed to keep being supported. It's also less readable and flexible. Overall, it's just not recommended to use. It's easy to get the WHERE clause incorrect and thus get far more rows than expected. – Tobberoth Dec 09 '13 at 10:03
  • 1
    SQL Ansi consortium is trying to get rid of those implicit joins (comma) for decades. But there are many legacy systems and many legacy programmers and that unlikely to happen. Go with the explicit joins, it let's the code a lot more clear. – jean Dec 09 '13 at 10:06
  • 1
    Does this answer your question? [INNER JOIN ON vs WHERE clause](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) – philipxy Mar 15 '21 at 22:56

2 Answers2

6

There's no real difference WHEN executing them, but there is a readability, consistency and error mitigating issue at work:

Imagine you had 4 tables If you used the old fashioned way of doing an INNER JOIN, you would end up with:

SELECT col1, col2
FROM tab1, tab2, tab3,tab4
WHERE tab1.id=tab2.tab1_id
AND tab4.id = tab2.tab3_id
AND tab4.id = tab3.tab4_id;

Using explicit INNER JOINS it would be:

SELECT col1, col2
FROM tab1
INNER JOIN tab2 ON tab1.id = tab2.tab1_id
INNER JOIN tab3 ON tab3.id = tab2.tab3_id
INNER JOIN tab4 ON tab4.id = tab3.tab4_id;

The latter shows you right in front of the table exactly what is it JOINing with. It has improved readability, and much less error prone, since it's harder to forget to put the ON clause, than to add another AND in WHERE or adding a wrong condition altogether (like i did in the query above :).

Additionally, if you are doing other types of JOINS, using the explicit way of writing them, you just need to change the INNER to something else, and the code is consistently constructed.

Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
2

Based on the specific code you gave, there is no difference at all.

However, using the JOIN operator syntax, you are allowed to specify the join conditions, which is very important when doing LEFT JOINs or RIGHT JOINs

Matteo Tassinari
  • 18,121
  • 8
  • 60
  • 81
  • There only one real difference, joins are evaluated before the conditional clause. There are very rare cases where a outter join ill lead to differente results. – jean Dec 09 '13 at 10:04