Posts SQL多表查询
Post
Cancel

SQL多表查询

一、 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

1
2
3
4
# union 筛选不同值
SELECT ID,Name FROM Students
UNION
SELECT ID,Name FROM Teachers
1
2
3
4
# union all 保留所有值
SELECT Name FROM Students
UNION ALL
SELECT Name FROM Teachers

二、 INNER JOIN(内连接),根据两个或多个表中的列之间的关系,从这些表中查询数据。

1
2
3
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students INNER JOIN Majors
ON Students.MajorID = Majors.ID

三、外连接分为三种:左外连接,右外连接,全外连接。 对应SQL:LEFT/RIGHT/FULL OUTER JOIN。

1
2
3
4
结果集保留左表的所有行,但只包含第二个表与第一表匹配的行。第二个表相应的空行被放入NULL值。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students LEFT JOIN Majors
ON Students.MajorID = Majors.ID
Students.IDStudents.NameMajors.Name
1JoyeYel
2EmiyNULL
1
2
3
4
右外连接保留了第二个表的所有行,但只包含第一个表与第二个表匹配的行。第一个表相应空行被入NULL值。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students RIGHT JOIN Majors
ON Students.MajorID = Majors.ID
Students.IDStudents.NameMajors.Name
1JoyeYel
NULLNULLPidy
1
2
3
4
会把两个表所有的行都显示在结果表中
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students FULL JOIN Majors
ON Students.MajorID = Majors.ID
Students.IDStudents.NameMajors.Name
1JoyeYel
3WuieLue
This post is licensed under CC BY 4.0 by the author.