SQL知识之多表查询

1、表的并集（UNION）

• UNION用于把许多SELECT语句的结果组合到一个结果集合中，也叫联合查询。
• 在多个SELECT 语句中，第一个SELECT语句中使用的字段名将作为结果集合的字段名。
• 使用 UNION 会将重复的记录进行剔除，而使用 UNION ALL 会将所有记录返回，效率高于UNION，所以一般先去重再使用 UNION ALL 进行联合查询可提高效率。
• 联合查询是行数的叠加，和字段的类型无关，只要求每个SELECT 语句查询的字段数一样，能对应即可
• 建议联合查询每个SELECT语句对应位置的字段含义一样，如果字段的含义不一样，叠加起来就没有意义了。

SELECT id, name, age FROM teacher
UNION
SELECT age, name, id FROM student;

SELECT id, name, age FROM teacher
UNION ALL
SELECT age, name, id FROM student;

2、联结查询

2.1 笛卡尔积

• 联结查询的原理是笛卡尔积（笛卡尔积是没有连接条件关系时多表查询返回的结果）
• 联结时首先确定数据需要用到哪些表，将这些表通过笛卡尔积变成一个表，然后根据两个表的关系去掉不符合逻辑的数据，最后通过筛选条件返回需要的结果。

SELECT * FROM tercher, student;       # 没有任何连接条件，返回的是笛卡尔积
SELECT * FROM tercher INNER JOIN student;       # 即使使用了联结，但是没有指定连接条件，返回的也是笛卡尔积

2.2 内联结（INNER JOIN）

INNER JOIN（INNER 可省略），根据筛选条件，在笛卡尔积的记录中返回所有匹配的数据记录，舍弃不匹配的数据记录。

内联结查询到的是一个符合ON条件匹配的在两个表中都存在记录的结果集。

SELECT * FROM tercher 
JOIN student
ON tercher.id = student.id;     # 注意，联结的字段不一定要同名

# 下面的写法属于隐性内联结，效果和INNER JOIN相同，但是不建议这么使用。
# 因为它是两表联结得到最终结果之后再进行筛选，而 INNER JOIN 是在中间过程筛选，得到最终结果。
SELECT * FROM tercher, student WHERE tercher.id = student.id; 

2.3 外联结（OUTER JOIN）

OUTER JOIN（OUTER 可省略），返回至少一张表的所有数据记录。

• LEFT JOIN，左联结，以左边的表为基准表进行联结，除了返回所有匹配的数据记录之外，还会返回左边表没有匹配上的记录，也就是会返回所有左边表的记录。
• RIGHT JOIN，右联结，以右边的表为基准表进行联结，除了返回所有匹配的数据记录之外，还会返回右边表没有匹配上的记录，也就是会返回所有右边表的记录。
• FULL JOIN，全联结，除了返回所有匹配的数据记录之外，还会返回左边和右表没有匹配上的记录。（MySQL不支持全联结）
• 左联结查询到的是一个包含所有左表记录和符合ON匹配的右表记录的结果集，如果左表的某条记录和右表的多条记录匹配，则结果集中就存在同一个左表记录对应多个右表记录的多条记录，此时的结果集记录数是大于左表的记录数的。对于右联结查询同理。

SELECT * FROM tercher 
LEFT JOIN student
ON tercher.id = student.id;

SELECT * FROM tercher 
RIGHT JOIN student
ON tercher.id = student.id;

SELECT * FROM tercher 
FULL  JOIN student
ON tercher.id = student.id;