the difference between comma and join in sql

Question

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;

Answer 1

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.

Answer 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