sql中逗号和join的区别 [英] the difference between comma and join in sql

问题描述

在两个不同的表之间使用逗号或连接有什么区别.

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

比如这两个代码:

SELECT studentId, tutorId FROM student, tutor;


SELECT studentId, tutorId FROM student JOIN tutor;

推荐答案

执行它们时没有真正的区别，但在工作中存在可读性、一致性和错误缓解问题:

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

假设你有 4 张桌子如果您使用老式的方式进行 INNER JOIN，您会得到:

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;

使用显式 INNER JOINS 将是:

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;

后者在桌子前面向您展示了它到底是用什么来连接的.它提高了可读性，并且更不容易出错，因为忘记放置 ON 子句比在 WHERE 中添加另一个 AND 或完全添加错误条件(就像我在上面的查询中所做的那样:)更难.

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 :).

另外，如果你在做其他类型的JOINS，使用显式的写法，你只需要把INNER改成别的东西，代码是一致构造的.

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.

这篇关于sql中逗号和join的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！