sql多表查询;内连接,外连接,交叉连接,自然连接以及using的使用

我们先理解什么是单表查询什么是多表查询?

第一、单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。

以下sql语句就是常见的单表查询:

select 字段名 from 表名 where 条件;

多表查询即为2个或两个以上的表查询

第二、两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

第三、多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

理解SQL查询的过程是进行SQL优化的理论依据。

连接运算符是用来实现多表联合查询的一种重要方式，主要分为三种：内连接、外连接、交叉连接。

其中内连接根据所使用的比较方式不同，内连接又分为等值连接、自然连接和自连接三种

在学习理论前,我们先新建两张表：

表1：student表 截图如下：

表2：course表  截图如下：

一、内连接

1.等值内连接inner join(常用)

内连接返回的两个表的公共记录

-- 语法一
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
-- 内连接中inner可以省略
select * from 表1 join 表2 on 表1.公共字段=表2.公共字段
-- 语法二
select * from 表1，表2 where 表1.公共字段=表2.公共字段

案例1:等值内连接--返回的两个表的公共记录

执行内连接sql语句

select * from student inner join course on student.ID=course.ID

执行结果:

inner join 是比较运算符，只返回符合条件的行。

相当于:

select * from student,course where student.ID=course.ID

案例2:

-- 让相同的字段只显示一次

mysql> select s.stuno,stuname,stusex,writtenexam,labexam from student s inner join course m on s.ID=m.ID;  //s m指的是别名

-- 使用where

mysql> select * from student,course where student.ID=course.ID;

-- 如何实现三表查询(表连接越多，效率越低)

select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 inner join 表3 on 表2.公共字段=表3.公共字段

等值内连接思考:

select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 inner join 表1 on 表1.公共字段=表2.公共字段   一样吗？

答：一样的

2.自然连接natural join

定义:等值连接中去掉重复的列，形成的连接。

自动判断条件连接，判断的条件是依据同名字段

说真的，这种连接查询没有存在的价值，既然是SQL2标准中定义的，就给出个例子看看吧。自然连接无需指定连接列，SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。不允许使用ON语句，不允许指定显示列，显示列只能用*表示（ORACLE环境下测试的）。对于每种连接类型（除了交叉连接外），均可指定NATURAL。

自然连接分又为自然内连接、自然外连接、自然右外连接

1、自然内连接（natural join）

mysql> select * from stuinfo natural join stumarks;

2、自然左外连接（natural left join）

mysql> select * from stuinfo natural left join stumarks;

3、自然右外连接（natural right join）

mysql> select * from stuinfo natural right join stumarks;

自然连接小结:

1、表连接是通过同名字段来连接的

2、如果没有同名字段就返回笛卡尔积

3、同名的连接字段只显示一个，并且将该字段放在最前面

3.自连接

 如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。

--c1、c2逻辑上是两张表，物理上是一张表 SELECT    c1.CategoryID,
    c1.CategoryNameFROM
    [dbo].[Category] c1INNER JOIN [dbo].[Category] c2 ON c1.[CategoryID] = c2.[ParentID]

二、外连接

外连接分为左外连接、右外连接、完全连接

1.左外连接:left join

定义:以左边的表为准，右边如果没有对应的记录用null显示

连接符:left join 或 left outer join

语法:

select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段

案例:

执行sql语句:

select * from student left join course on student.ID=course.ID

执行结果:

左外连接思考:

select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 left join 表1 on 表1.公共字段=表2.公共字段   一样吗？

答：不一样，第一个SQL以表1为准，第二个SQL以表2为准。

小结:

左外连接包含left join左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL).

注：此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。

2.右外连接right join

定义:以右边的表为准，左边如果没有对应的记录用null显示

连接符:right join 或 right outer join

语法:

和左外连接类似

select * from 表1 right join 表2 on 表1.公共字段=表2.公共字段

案例:

执行sql语句:

select * from student right join course on student.ID=course.ID

执行结果:

右外连接思考:

select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 right join 表1 on 表1.公共字段=表2.公共字段   一样吗？

答：一样

小结:

右外连接包含right join右表所有行，如果左表中某行在右表没有匹配，则结果中对应左表的部分全部为空(NULL)。

注：同样此时我们不能说结果的行数等于右表的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。

3.完全外连接full join

定义:完全外连接包含full join左右两表中所有的行，如果右表中某行在左表中没有匹配，则结果中对应行右表的部分全部为空(NULL)，如果左表中某行在右表中没有匹配，则结果中对应行左表的部分全部为空(NULL)。

连接符:full join 或 full outer join

语法:

select * from 表1 full join 表2 on 表1.公共字段=表2.公共字段

案例:

执行sql语句:

select * from student full join course on student.ID=course.ID

执行结果:

三、交叉连接cross join

定义:返回笛卡尔积

没有 WHERE 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

连接符:cross join

语法:

select * from 表1 cross join 表2

案例:

执行sql语句:

select * from student cross join course

执行结果:

如果我们在此时给这条SQL加上WHERE子句或on子句的时候比如SQL:

select * from student cross join course where student.ID=course.ID
或
select * from student cross join course on student.ID=course.ID

此时将返回符合条件的结果集，结果和内连接的inner join所示执行结果一样。

小结:

1、交叉连接如果没有连接条件返回笛卡尔积

2、如果有连接条件和内连接是一样的。

四、using的使用

作用:

using用来指定连接字段

案例:

mysql> select * from student inner join cross using(ID);

using的结果也会对公共字段进行优化，优化的规则和自然连接是一样的；

拓展练习:

练习1:显示地区及每个地区参加笔试的人数，并按人数降序排列

-- 第一步： 显示地区及每个地区参加笔试的人数
mysql> select stuaddress,count(writtenexam) from stuinfo left join stumarks using(stuno) group by stuaddress;
-- 第二步：将结果降序排列
mysql> select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress order by c desc;

练习2:显示有学生参加考试的地区

-- 方法1:having筛选实现
mysql> select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress having c>0;

-- 方法2:表连接实现
-- 第一步：右连接获取有成绩的地区
mysql> select stuaddress from stuinfo right join stumarks using(stuno);
+------------+
| stuaddress |
+------------+
| 北京           |
| 上海          |
| 天津           |
| 北京           |
| 天津           |
| NULL       |
+------------+
6 rows in set (0.00 sec)
-- 第二步：去重复
mysql> select distinct stuaddress from stuinfo right join stumarks using(stuno);
+------------+
| stuaddress |
+------------+
| 北京           |
| 上海          |
| 天津           |
| NULL       |
+------------+
4 rows in set (0.00 sec)

-- 去除null
mysql> select distinct stuaddress from stuinfo right join stumarks using(stuno) having stuaddress is not null;
+------------+
| stuaddress |
+------------+
| 北京           |
| 上海          |
| 天津           |
+------------+
3 rows in set (0.00 sec)

练习3:显示男生和女生的人数

-- 方法一： 分组查询
mysql> select stusex,count(*) from stuinfo group by stusex;
+--------+----------+
| stusex | count(*) |
+--------+----------+
| 女      |        3 |
| 男       |        4 |
+--------+----------+
2 rows in set (0.00 sec)

-- 方法二： union
mysql> select stusex,count(*) from stuinfo where stusex='男' union select stusex,count(*) from stuinfo where stusex='女';
+--------+----------+
| stusex | count(*) |
+--------+----------+
| 男       |        4 |
| 女      |        3 |
+--------+----------+
2 rows in set (0.00 sec)

-- 方法三：直接写条件

mysql> select sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo;
+------+------+
| 男     | 女    |
+------+------+
|    4 |    3 |
+------+------+
1 row in set (0.00 sec)

练习4:显示每个地区男生、女生、总人数

mysql> select stuaddress,count(*) 总人数,sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo group by stuaddress;
+------------+--------+------+------+
| stuaddress | 总人数       | 男     | 女    |
+------------+--------+------+------+
| 上海          |      1 |    1 |    0 |
| 北京           |      2 |    1 |    1 |
| 天津           |      2 |    2 |    0 |
| 河北          |      1 |    0 |    1 |
| 河南           |      1 |    0 |    1 |
+------------+--------+------+------+
5 rows in set (0.00 sec)

案例补充:

内连接、左右连接和全连接的区别

假设您有两个表，每个表只有一个列，表数据如下

A    B
-    -
1    3
2    4
3    5
4    6

内连接

内连接是A表的所有行交上B表的所有行得出的结果集

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4

左外连接

左外连接是A表的所有行匹配上B表得出的结果集

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);
a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

右外连接

右外连接是B表的所有行匹配上A表得出的结果集

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;
 
a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

全连接

全连接是A表的所有行并上B表的所有行得出的结果集

select * from a FULL OUTER JOIN b on a.a = b.b;
a   |  b
-----+-----
1 | null
2 | null
3 |    3
4 |    4
null |    6
null |    5