SQL- join多表关联

深碍√TFBOYSˉ_ 2023-10-01 11:48 21阅读 0赞

### 一、SQL 连接(JOIN) ###

1、笛卡尔积

（1）当多张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是多张表条数的乘积

如A表15条（行）数据，B表20条（行）数据，结果查询两张表时，会产生 15 \* 20 = 300条（行）数据

select empname,deptname from emp, dept;

（2）避免笛卡尔积现象

select 
    		empname,deptname 
    	from 
    		emp, dept
    	where
    		emp.deptno = dept.deptno;
    
    	
    // 或者
    	select 
    		e.empname,d.deptname 
    	from 
    		emp e, dept d
    	where
    		e.deptno = d.deptno; //SQL92语法

最终得出结果会减少，但是查询次数依然是两张表行数的乘积

因此：**通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数**

2、SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段（**跨表查询**）

SQL92：1992年的SQL语法  
SQL99：1999年的SQL语法

从一张表中单独查询，称为**单表查询**

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70][]

3、SQL JOIN 类型

（1）INNER JOIN：内连接

【1】等值连接，返回两个表中连接字段相等的行（条件是等量关系）

【2】非等值连接，条件不是一个等量关系

【3】自连接，同一张表看成多张表

注：

INNER 可以省略

两张表没有主次关系；平等

（2）OUTER JOIN ：外连接

LEFT (OUTER) JOIN：左（外）连接，即使右表中没有匹配，也从左表返回所有的行（将join关键字左边的表看成主表，主要是为了将左表的数据全部查询出来，捎带着关联查询右边的表）

RIGHT (OUTER) JOIN：右（外）连接，即使左表中没有匹配，也从右表返回所有的行（将join关键字右边的表看成主表，主要是为了将右表的数据全部查询出来，捎带着关联查询左边的表）

FULL (OUTER) JOIN ：全（外）连接

外连接，只要其中一个表中存在匹配，则返回；即返回两个表中的行：left join + right join

注：

OUTER 可以省略

在外连接当中，两张表连接，产生了主次关系

（3）交叉连接

CROSS JOIN: 结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数

4、SQL INNER JOIN

INNER JOIN 关键字在表中存在至少一个匹配时返回行

SELECT column_name(s)
    FROM table1
    INNER JOIN table2
    ON table1.column_name=table2.column_name;
     
    或者
     
    SELECT column_name(s)
    FROM table1
    JOIN table2
    ON table1.column_name=table2.column_name;
    INNER JOIN 与 JOIN 是相同的

（1）等值连接

SQL92语法：
    	select 
    		e.ename,d.dname
    	from
    		emp e, dept d
    	where
    		e.deptno = d.deptno;
    //sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。
    
    SQL99语法：
    	select 
    		e.ename,d.dname
    	from
    		emp e
    	join
    		dept d
    	on
    		e.deptno = d.deptno;
    	
    
    	//inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）
    	select 
    		e.ename,d.dname
    	from
    		emp e
    	inner join
    		dept d
    	on
    		e.deptno = d.deptno; // 条件是等量关系，所以被称为等值连接。
    //sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

inner可以省略，带着inner可读性更好

sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面

sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

（2）非等值连接

select 
    	e.ename, e.sal, s.grade
    from
    	emp e
    join
    	salgrade s
    on
    	e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。
    
    
    select 
    	e.ename, e.sal, s.grade
    from
    	emp e
    inner join
    	salgrade s
    on
    	e.sal between s.losal and s.hisal;

（3）自连接

一张表看成两张表

select 
    	a.ename as '员工名', b.ename as '领导名'
    from
    	emp a
    join
    	emp b
    on
    	a.mgr = b.empno; //员工的领导编号 = 领导的员工编号

INNER JOIN 与 JOIN 是相同的

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 1][]

5、SQL LEFT JOIN

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL

将join关键字左边的表看成主表，主要是为了将左表的数据全部查询出来，捎带着关联查询右边的表

SELECT column_name(s)
    FROM table1
    LEFT JOIN table2
    ON table1.column_name=table2.column_name;
     
    或
     
    SELECT column_name(s)
    FROM table1
    LEFT OUTER JOIN table2
    ON table1.column_name=table2.column_name;
    在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN

如下：

select 
    	e.ename,d.dname
    from
    	dept d 
    left (outer) join 
    	emp e
    on
    	e.deptno = d.deptno;

在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 2][]

**关键字 on**   
**数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户**

在使用 left jion 时，on 和 where 条件的区别如下：

（1） on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。  
（2）where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 3][]

两条 SQL:

select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'
     
    select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 4][] 以上结果的关键原因就是 left join、right join、full join 的特殊性，不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则具有 left 和 right 的特性的并集。 而 inner jion 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的。

【6】SQL RIGHT JOIN

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL

将join关键字右边的表看成主表，主要是为了将右表的数据全部查询出来，捎带着关联查询左边的表

SELECT column_name(s)
    FROM table1
    RIGHT JOIN table2
    ON table1.column_name=table2.column_name;
     
    或
     
    SELECT column_name(s)
    FROM table1
    RIGHT OUTER JOIN table2
    ON table1.column_name=table2.column_name;
    在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN

如下：

select 
    	e.ename,d.dname
    from
    	emp e 
    right (outer) join 
    	dept d
    on
    	e.deptno = d.deptno;

在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 5][]

【7】SQL FULL OUTER JOIN

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果（MySQL中不支持 FULL OUTER JOIN）

SELECT column_name(s)
    FROM table1
    FULL OUTER JOIN table2
    ON table1.column_name=table2.column_name;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 6][]

总结如下：

A inner join B 取交集。

A left join B 取 A 全部，B 没有对应的值为 null。

A right join B 取 B 全部 A 没有对应的值为 null。

A full outer join B 取并集，彼此没有对应的值为 null

如： "user" 表中的 "deptId" 列指向 "dept" 表中的字段 "id"；上面这两个表是通过 "deptId" 列联系起来的

select u.id,d.id,d.name,d.number 
    from user u left join dept d 
    on u.deptId = d.id;
     
    或
     
    select u.id,u.name,d.id,d.name,d.number 
    from user u inner join dept d 
    on u.deptId = d.id;

查询结果相同

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 7][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210711222033302.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210711223808485.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210711223950218.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210711225257705.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20210711225327436.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20210711224135599.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20210711224317962.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20210711223215685.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu,size_16,color_FFFFFF,t_70