SQL总结（二）连表查询

连接查询包括合并、内连接、外连接和交叉连接，如果涉及多表查询，了解这些连接的特点很重要。

只有真正了解它们之间的区别，才能正确使用。

1、Union

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

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。

当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

注意：使用UNION时，两张表查询的结果有相同数量的列、列类型相似。

学生表信息（Students）：

ID	Name	Age	City	MajorID
101	Tom	20	BeiJing	10
102	Lucy	18	ShangHai	11

 

 

 

教师表信息（Teachers）：

ID	Name
101	Mrs Lee
102	Lucy

 

 

 

预置脚本：

INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,‘Tom‘,20,‘BeiJing‘,10)
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(102,‘Lucy‘,18,‘ShangHai‘,11)

INSERT INTO Teachers(ID,Name) VALUES(101,‘Mrs Lee‘)
INSERT INTO Teachers(ID,Name) VALUES(102,‘Lucy‘)

 

1）基本UNION查询，查询学校教师、学生的总的信息表，包括ID和姓名

SELECT ID,Name FROM Students
UNION
SELECT ID,Name FROM Teachers

查询结果：

ID	Name
101	Mrs Lee
101	Tom
102	Lucy

 

 

 

 2)带条件的UNION查询，也可以查询同一张表，查询年龄为18，23岁的学生信息

SELECT ID,Name FROM Student WHERE Age=18
UNION
SELECT ID,Name FROM Student WHERE Age=23

当然，这可以使用IN或者OR很容易实现，这里只是点到，以后遇到复杂查询，相信你会用到。

3）查询教师学生全部姓名

 因为UNION只会选择不同的值，如果学生中和教师中有重名的情况，这就需要UNION ALL

SELECT Name FROM Students
UNION ALL
SELECT Name FROM Teachers

查询结果：

ID	Name
101	Tom
102	Lucy
101	Mrs Lee
102	Lucy

 

 

 

  

2、INNER JOIN（内连接）

INNER JOIN（内连接），也成为自然连接

作用：根据两个或多个表中的列之间的关系，从这些表中查询数据。

注意: 内连接是从结果中删除其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

重点：内连接，只查匹配行。

语法：(INNER可省略)

SELECT fieldlist
FROM table1 [INNER] join table2
ON table1.column=table2.column

学生表信息（Students）：

ID	Name	Age	City	MajorID
101	Tom	20	BeiJing	10
102	Lucy	18	ShangHai	11

 

 

 

专业信息表（Majors）： 

ID	Name
10	English
12	Computer

 

 

 

 预置脚本：

CREATE DATABASE TestDB

USE TestDB

------------------------------------------
--创建相关表
IF OBJECT_ID(‘Students‘,‘U‘) IS NOT NULL
DROP TABLE Students
--学生信息表
CREATE TABLE Students(
ID int primary key not null,
Name nvarchar(50),
Age int,
City nvarchar(50),
MajorID int
)


--专业信息表
IF OBJECT_ID(‘Majors‘,‘U‘) IS NOT NULL
DROP TABLE Majors

CREATE TABLE Majors(
ID int primary key not null,
Name nvarchar(50)
)

--教师信息表
IF OBJECT_ID(‘Teachers‘,‘U‘) IS NOT NULL
DROP TABLE Teachers
CREATE TABLE Teachers(
    ID int primary key not null,
    Name nvarchar(20) not null
)

--预置数据
DELETE FROM Students
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,‘Tom‘,20,‘BeiJing‘,10)
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(102,‘Lucy‘,18,‘ShangHai‘,11)

DELETE FROM Majors
INSERT INTO Majors(ID,Name) VALUES(10,‘English‘)
INSERT INTO Majors(ID,Name) VALUES(12,‘Computer‘)

DELETE FROM Teachers
INSERT INTO Teachers(ID,Name) VALUES(101,‘Mrs Lee‘)
INSERT INTO Teachers(ID,Name) VALUES(102,‘Lucy‘)



SELECT ID,Name FROM Students
UNION
SELECT ID,Name FROM Teachers

SELECT ID,Name FROM Students
UNION ALL
SELECT ID,Name FROM Teachers

--内连接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students INNER JOIN Majors
ON Students.MajorID = Majors.ID

--左连接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students LEFT JOIN Majors
ON Students.MajorID = Majors.ID

--右连接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students LEFT JOIN Majors
ON Students.MajorID = Majors.ID

--全连接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students FULL JOIN Majors
ON Students.MajorID = Majors.ID

--交叉连接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors

--交叉连接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors
WHERE Students.MajorID = Majors.ID

--一次查询多表
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students,Majors

View Code

 

 

SQL总结（二）连表查询,布布扣,bubuko.com