简介 ：在一次练习中发现的小知识点，找了一圈发现没有人在说，都在复制粘贴啊。因此，记录下来。

这标题够不够UC哈哈哈哈哈

引言

众所周知（不知也要知），MySQL语句中，JOIN可以将两个表甚至更多表联起来使用。最基础的就是JOIN ON

1
2
3
4
SELECT *
FROM 表名1 o
JOIN 表名2 p
    ON o.uuu_id = p.uuu_id

当两个表中要充当中间连接的列名一样（uuu_id），就可以用USING替代：

1
2
3
4
SELECT *
FROM 表名1 o
JOIN 表名2 p
    USING (uuu_id)

正文

关于这件事情的起始源于我刷的一道题让我注意到了这个问题，题目如下：

SQL34 统计复旦用户8月练题情况: 点击查看原题

描述
题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

示例：用户信息表user_profile

id device_id gender age university gpa active_days_within_30
1 2138 male 21 北京大学 3.4 7
2 3214 male 复旦大学 4.0 15
3 6543 female 20 北京大学 3.2 12
4 2315 female 23 浙江大学 3.6 5
5 5432 male 25 山东大学 3.8 20
6 2131 male 28 山东大学 3.3 15
7 4321 female 26 复旦大学 3.6 9

示例：question_practice_detail

id device_id question_id result date
1 2138 111 wrong 2021-05-03
2 3214 112 wrong 2021-05-09
3 3214 113 wrong 2021-06-15
4 6543 111 right 2021-08-13
5 2315 115 right 2021-08-13
6 2315 116 right 2021-08-14
7 2315 117 wrong 2021-08-15
……

根据示例，你的查询应返回以下结果：

device_id university question-cnt right_question_cnt
3214 复旦大学 3 0
4321 复旦大学 0 0

写题过程省略…直接跳到重点

这是正确解答：

1
2
3
4
5
6
7
8
9
10
SELECT 
    up.device_id,
    university,
    COUNT(qpd.device_id) AS question_cnt,
    SUM(IF(qpd.result = 'right', 1, 0)) AS right_question_cnt
FROM user_profile up
LEFT JOIN question_practice_detail qpd ON up.device_id = qpd.device_id
    AND MONTH(qpd.date) = 8
WHERE university = '复旦大学'
GROUP BY up.device_id

第7行的ON替换成 USING 按理来说是可以使用的，但却报错了

1
2
3
4
5
6
7
8
9
10
SELECT 
    up.device_id,
    university,
    COUNT(qpd.device_id) AS question_cnt,
    SUM(IF(qpd.result = 'right', 1, 0)) AS right_question_cnt
FROM user_profile up
LEFT JOIN question_practice_detail qpd USING(device_id)
    AND MONTH(qpd.date) = 8
WHERE university = '复旦大学'
GROUP BY up.device_id

官方文档中对USING的使用的解释大致就是说列明相同能用USING

  • The USING(*join_column_list*) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:
1
a LEFT JOIN b USING (c1, c2, c3)

结论：目前来看，ON的使用方法更为丰富也更多；USING后面应该是不能跟AND连接使用的，使用USING时后面尽量不要连接其他语句，以免发生未知错误，除非是非常熟悉该语句。

当然也不一定非要这样使用……

方法补充

可以将and移到where中这样写同样有效，同时要注意

1
2
3
4
5
6
7
8
9
SELECT 
    up.device_id,
    university,
    COUNT(qpd.device_id) AS question_cnt,
    SUM(IF(qpd.result = 'right', 1, 0)) AS right_question_cnt
FROM user_profile up
LEFT JOIN question_practice_detail qpd USING (device_id)
WHERE university = '复旦大学' AND (MONTH(qpd.date)=8 or date IS NULL)
GROUP BY up.device_id

那么问题来了，为什么 JOIN…ON…后面的date不用排除NULL就能得到正确答案，而WHERE后面就需要排除NULL呢？