【MySQL高级】7种join语句

你的名字 2021-10-03 01:56 304阅读 0赞

join语句是面试中经常会让你现场写出来的语句，小白今天在这里对join语句进行总结：

1. 内连接

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70][]

select <select\_list>  
    from tableA A  
    inner join tableB B  
    on A.key = B.key

2.左连接

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 1][]

select <select\_list>  
    from tableA A  
    left join tableB B  
    on A.key = B.key

3.右连接

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 2][]

select <select\_list>  
    from tableA A  
    right join tableB B  
    on A.key = B.key

4.左连接（A独有）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 3][]

select <select\_list>  
    from tableA A  
    left join tableB B  
    on A.key = B.key  
    where b.key = null;

5.右连接（B独有）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 4][]

select <select\_list>  
    from tableA A  
    left join tableB B  
    on A.key = B.key  
    where a.key = null;

6.全连接

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 5][]

select <select\_list>  
    from tableA A  
    full outer join TableB B  
    on A.key = B.key

7.独有连接（A，B的独有）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 6][]

select <select\_list>  
    from tableA A  
    full outer join TableB B  
    on A.key = B.key  
    where A.key is null  
    or B.key is null

实战训练

1.进行建表以及插入数据

create table tbl_dept(
    	id int(11) not null AUTO_INCREMENT,
    	deptName varchar(30) default null,
    	locAdd varchar(40) default null,
    	primary key (id)
    )engine = innodb AUTO_INCREMENT = 1 default charset = utf8;
    create table tab_emp(
    	id int(11) not null AUTO_INCREMENT,
    	name varchar(20) default null,
    	deptId int(11) default null,
    	primary key(id),
    	key fk_dept_id(deptId)
    	#constraint fk_dept_id foreign key (deptId) references tbl_dept(id)
    )engine = innodb AUTO_INCREMENT = 1 default charset = utf8;	
    	
    insert into tbl_dept(deptName,locAdd) values('RD','11');
    insert into tbl_dept(deptName,locAdd) values('HR','12');
    insert into tbl_dept(deptName,locAdd) values('MK','13');
    insert into tbl_dept(deptName,locAdd) values('MIS','14');
    insert into tbl_dept(deptName,locAdd) values('FD','15');
    
    insert into tab_emp(name,deptId) values('z3',1);
    insert into tab_emp(name,deptId) values('z4',1);
    insert into tab_emp(name,deptId) values('z5',1);
    insert into tab_emp(name,deptId) values('w5',2);
    insert into tab_emp(name,deptId) values('w6',2);
    insert into tab_emp(name,deptId) values('s7',3);
    insert into tab_emp(name,deptId) values('s8',4);
    insert into tab_emp(name,deptId) values('s9',51);

2.进行连接查询

·进行内连接查询

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 7][]

·进行全连接查询，因为MySQL不支持全连接，所以这里使用union进行连接查询，而且union有去重的效果，使用union不去掉重复的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 8][]

进行独有查询

![20200401143714211.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70]: /images/20211003/92f52524ba5e4e91853aaf858f40981e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 1]: /images/20211003/c8206bdde8c042c6af1cdafc7963f947.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 2]: /images/20211003/772df5940fac4d119deb5074c5e3a3fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 3]: /images/20211003/10822de711b74116bccf2469a059ea1a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 4]: /images/20211003/9e737b93289b40c8917aab116e4e265f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 5]: /images/20211003/0d6d0e539b434243945b40243e0e345a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 6]: /images/20211003/028cdebed0d44089b1ca801113a8c353.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 7]: /images/20211003/324363afd91a4164b4aca951b1dc097e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3MTA4Ng_size_16_color_FFFFFF_t_70 8]: /images/20211003/9aff7f03addf42ab86a1493fce32e9e2.png
[20200401143714211.png]: /images/20211003/df2c8215ea6b48f783fcd31ec81cf901.png