MySQL-交叉连接 内连接 左连接 右连接 全连接详解

1.交叉连接

基本语法:

SELECT 字段名1 ...
FROM 表名1 CROSS JOIN 表名2;

表示返回两个表中所有数据行(元组)的笛卡尔积.通俗的来说,如果表1有n个数据行,表2有m个数据行,那么对表1和表2进行交叉连接返回的新表应当是这样的:
表1的第一行依次与表2的每一行组成新的m个数据行,
表1的第二行依次与表2的每一行组成新的m个数据行…
一直到表1的第n行依次与表2的每一行组成新的m个数据行.
容易得出,新表应当有m*n个数据行
创建这样的笛卡尔积通常是没有意义的,因此CROSS JOIN在实际应用中并不常用,这里就不举例子了,相信读者应当已经理解了.

2.内连接:

不同于交叉连接会将第一个表的每个元组与第二个表的所有元组都进行连接.内连接则只考虑在两个表中都出现的属性上取值相同的元组对.相当于交叉连接前先进行了筛选,比如筛选条件是两个表的ID相同.那么先筛选出ID为1的表1和表2中的数据行,然后对他们进行交叉连接.再筛选出ID为2的表1和表2中的数据行,然后对他们进行交叉连接,直到所有ID相同的数据行都被连接完.
基本语法:

SELECT 字段名1 ...
FROM 表名1

INNER JOIN 表名2
ON 表名1.关系字段=表名2.关系字段

INNER JOIN 表名3
ON 表名1.关系字段=表3.关系字段

注:表1通常包含在表2,表3
实例:
各学校的学生通过注册在某题库进行刷题,已知:
用户信息表user_profile:

id	device_id	gender	age	university
1	2138	male	21	北京大学
2	3214	male	NULL	复旦大学
3	6543	female	20	北京大学
4	2315	female	23	浙江大学
5	5432	male	25	山东大学
6	2131	male	28	山东大学
7	4321	male	28	复旦大学

题库练习明细表:question_practice_detail:

id	device_id	question_id	result
1	2138	111	wrong
2	3214	112	wrong
3	3214	113	wrong
4	6534	111	right
5	2315	115	right
6	2315	116	right
7	2315	117	wrong
8	5432	117	wrong
9	5432	112	wrong
10	2131	113	right
11	5432	113	wrong
12	2315	115	right
13	2315	116	right
14	2315	117	wrong
15	5432	117	wrong
16	5432	112	wrong
17	2131	113	right
18	5432	113	wrong
19	2315	117	wrong
20	5432	117	wrong
21	5432	112	wrong
22	2131	113	right
23	5432	113	wrong

题目细节表:question_detail:

id	question_id	difficult_level
1	111	hard
2	112	medium
3	113	easy
4	115	easy
5	116	medium
6	117	easy

题目:请你写一个SQL查询，计算不同学校、不同难度的用户平均答题量,返回university,difficult_level,avg_answer_cnt(结果保留四位小数)

SELECT 
    user_profile.university,
    question_detail.difficult_level,
    ROUND((COUNT(question_practice_detail.device_id)/COUNT(DISTINCT question_practice_detail.device_id)),4) as avg_answer_cnt
FROM question_practice_detail

INNER JOIN user_profile
ON question_practice_detail.device_id=user_profile.device_id

INNER JOIN question_detail
ON question_practice_detail.question_id=question_detail.question_id

GROUP BY user_profile.university,question_detail.difficult_level;

注:这道题目是可以用内连接来做的,但是相信如果你足够细心,你会发现内连接的缺点是他会遗漏掉某些表中的信息(前文说了因为他相当于先做了筛选),比如user_profile表中device_id为4321的复旦大学28岁的一个男性用户,因为在题库中没有他的答题信息所以被过滤掉了.那么如果我们想保留他,我们该怎么做呢,这就需要提到外连接.

3.左连接

左连接的结果包括LEFT JOIN 子句中指定的左表中的所有记录,以及所有满足连接条件的记录.如果左表的某条记录在右表中不存在,则在右表中显示为空.

基本语法:

SELECT 字段名1 ...
FROM 表名1

LEFT JOIN 表名2
ON 表名1.关系字段=表名2.关系字段

LEFT JOIN 表名3
ON 表名1.关系字段=表3.关系字段

4.右连接

右连接与左连接正好相反,返回右表中所有指定的记录和所有满足连接条件的记录,如果右表的某条记录在左表中没有匹配,则左表将返回空值.

基本语法:

SELECT 字段名1 ...
FROM 表名1

RIGHT JOIN 表名2
ON 表名1.关系字段=表名2.关系字段

5. 全连接

全连接是左连接与右连接的组合,在内连接结果计算出来之后,左侧关系中不匹配右侧关系任何元组的元组被添加上空值并加到结果中.类似的,右侧关系中不匹配左侧关系任何元组的元组也被添加上空值并加到结果中.