sql连接查询

柔情只为你懂 2023-10-05 22:34 50阅读 0赞

## 按功能分类： ##

> 内连接：
> 
> 等值连接、非等值连接、自连接
> 
> 外连接：
> 
> 左外链接、左链接
> 
> 右外链接、右链接
> 
> 完全外链接
> 
> 全连接
> 
> 交叉连接

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgwMjA0NA_size_16_color_FFFFFF_t_70][]

## 按年代分类 ##

> sql 92标准，又称sql2
> 
> sql 99标准【推荐】，又称sql3

## 交叉连接 ##

\--笛卡尔积（将两张表的数据拼接在一起,总记录数为两张表记录量的乘积 ( 表\*表 )

select * from a,b;

## 内连接 **INNER JOIN** ##

### 等值连接 ###

#92语法（查询A表人对应的妻子）
    select * from a,b where a.wife = b.id;
    #99语法
    select * from a inner join b on a.wife = b.id;

![7e929a3075784dbdac8455c86d9b7033.png][]

### 非等值连接 ###

#查询每个员工对应的工资等级
    select e.ename, s.grade 
    	from emp e 
    inner join salgrade s
          on e.sal between s.losal and s.hisal;

![17da36e05b944c52a3c497fd7e656e72.png][]

### 自连接 ###

#查询每个人的领导（一张员工表领导也是这个公司的员工）
    select e1.ename,'上司是',e2.ename
    	from emp e1
    inner join emp e2
    	on e1.mgr = e2.empno;

![37b841fd0d0f45c6b160d255ac3c193f.png][]

## 外连接： ##

### 左连接**LEFT JOIN** ###

select * from a left join b on a.wife = b.id;

![b753e8384e93476390cac278658da7ea.png][]

### 右连接**RIGHT JOIN** ###

select * from a right join b on a.wife = b.id;

![d5e0fa1daf0246b1aca811e88aaf8712.png][]

### 左外链接 ###

select * from a left join b on a.wife = b.id where b.id is null;

![0bc2d4abd7c0440cb1aa160046821099.png][]

### 右外链接 ###

select * from a right join b on a.wife = b.id where a.id is null;

![e7fb58ef76924826a259b5a84c91eb2a.png][]

### 完全外链接 ###

mysql不支持full join

select * from a left join b 
    	on a.wife = b.id where b.id is null
    	union all               #union all是将两个select语句的结果求并集
    select * from a right join b 
    	on a.wife = b.id where a.wife is null;

![6a256b9b48104805b87b2231fe1870df.png][]

## 全连接 ##

除了显示满足连接的条件的行外，还显示了join两侧表中所有满足检索条件的行

select * from a left join b 
    	on a.wife = b.id
    	union all
    select * from a right join b 
    	on a.wife = b.id;

![6c02f9fdbede43679e36a8868f2b67c8.png][]

另附数据表

CREATE TABLE EMP
           (EMPNO int(4)  not null ,
    	ENAME VARCHAR(10),
    	JOB VARCHAR(9),
    	MGR INT(4),
    	HIREDATE DATE  DEFAULT NULL,
    	SAL DOUBLE(7,2),
    	COMM DOUBLE(7,2),
    	primary key (EMPNO),
    	DEPTNO INT(2) 
    	)
    	;
    
    CREATE TABLE SALGRADE
          ( GRADE INT,
    	LOSAL INT,
    	HISAL INT );
    #自行加数据
    INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
    10, 'ACCOUNTING', 'NEW YORK'); 
    
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
    , 800, NULL, 20);

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgwMjA0NA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201025221254283.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgwMjA0NA==,size_16,color_FFFFFF,t_70#
[7e929a3075784dbdac8455c86d9b7033.png]: https://img-blog.csdnimg.cn/7e929a3075784dbdac8455c86d9b7033.png
[17da36e05b944c52a3c497fd7e656e72.png]: https://img-blog.csdnimg.cn/17da36e05b944c52a3c497fd7e656e72.png
[37b841fd0d0f45c6b160d255ac3c193f.png]: https://img-blog.csdnimg.cn/37b841fd0d0f45c6b160d255ac3c193f.png
[b753e8384e93476390cac278658da7ea.png]: https://img-blog.csdnimg.cn/b753e8384e93476390cac278658da7ea.png
[d5e0fa1daf0246b1aca811e88aaf8712.png]: https://img-blog.csdnimg.cn/d5e0fa1daf0246b1aca811e88aaf8712.png
[0bc2d4abd7c0440cb1aa160046821099.png]: https://img-blog.csdnimg.cn/0bc2d4abd7c0440cb1aa160046821099.png
[e7fb58ef76924826a259b5a84c91eb2a.png]: https://img-blog.csdnimg.cn/e7fb58ef76924826a259b5a84c91eb2a.png
[6a256b9b48104805b87b2231fe1870df.png]: https://img-blog.csdnimg.cn/6a256b9b48104805b87b2231fe1870df.png
[6c02f9fdbede43679e36a8868f2b67c8.png]: https://img-blog.csdnimg.cn/6c02f9fdbede43679e36a8868f2b67c8.png