Discuss / SQL / INNER JOIN 是否可以用 WHERE 条件代替
Back

INNER JOIN 是否可以用 WHERE 条件代替

Topic source

黑-白_GREY

#1 Created at ... [Delete] [Delete and Lock User]

老师，上面的 INNER JOIN 连接查询

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

和用 WHERE students.class_id=classes.id 的条件查询：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s,
classes c
WHERE s.class_id=c.id;

结果相同，那一般常用的 INNER JOIN 查询方式是不是可以用 WHERE 的方式替代呢？

廖雪峰

#2 Created at ... [Delete] [Delete and Lock User]

FROM x, y就是笛卡尔积，两个1万行的表就会变成1亿行，效率太低

xian_wen

#3 Created at ... [Delete] [Delete and Lock User]

soga，不用 where 是因为低效，这才是本质

MC大羊羊羊羊羊

#4 Created at ... [Delete] [Delete and Lock User]

FROM x, y后加入where条件，是不是就是隐式内连接，和INNER JOIN 的写法效率一样吗

念夜未央-

#5 Created at ... [Delete] [Delete and Lock User]

FROM X,Y是先做笛卡尔乘积，WHERE 再从乘积后的表中选出指定列，

直接INNERE JOIN可以从一个主表 “附加” 副表的一列，效率更高点

🌙

#6 Created at ... [Delete] [Delete and Lock User]

原来 答案 在这里  以后 就用内连接了

  • 1

Reply