mysql ：join连接 （7种）

前言

关于MySQL 中 的JOIN 连接，今天在这里进行记录

一：建表

create table tbl_dept(
  id  int primary key  not null auto_increment,
	deptName varchar(30) default null  #部门名
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



 insert into tbl_dept(deptName) values('RD');
 insert into tbl_dept(deptName) VALUES('HR');
 insert into tbl_dept(deptName) VALUES('MK');
 SELECT * from tbl_dept;
 

create table tbl_emp(
  	id  int primary key  not null auto_increment,
	name  varchar(30) default null ,
	deptId int default null,
	CONSTRAINT fk_dept_id FOREIGN KEY (deptId) REFERENCES tbl_dept(id)  # 外键 
	

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 INSERT into tbl_emp(name,deptId) VALUES ('z3',1);
  
 INSERT into tbl_emp(name,deptId) VALUES ('l4',3);
  
 INSERT into tbl_emp(name,deptId) VALUES ('w5',2);
  
 INSERT into tbl_emp(name,deptId) VALUES ('d6',30);


 SELECT * from tbl_emp;

二、七种连接

1.笛卡尔积

a*b

 select  * from  tbl_emp a ,tbl_dept b; 

2 . INNER JOIN

a和b 的交集

select  * from  tbl_emp a  INNER JOIN tbl_dept b on a.deptId = b.id; 	

3. LEFT JOIN

a 表的全部

 select  * from  tbl_emp a  LEFT JOIN tbl_dept b on a.deptId = b.id ;
					
 

4. RIGHT JOIN

b 的全部；

 select  * from  tbl_emp a  RIGHT JOIN tbl_dept b on a.deptId = b.id ;
			 

5. 全连接

a 和 b 的并集；

ｆｕｌｌ　ｊｏｉｎ　ｍｙｓｑｌ　好像不支持；

 select  * from  tbl_emp a  FULL JOIN  tbl_dept b on a.deptId = b.id ;	#

换一种思路：
就是　左连接　加上　右连接　　并去重　　
ＵＮＩＯＮ

  select  * from  tbl_emp a  LEFT JOIN tbl_dept b on a.deptId = b.id 
UNION
 select  * from  tbl_emp a  RIGHT JOIN tbl_dept b on a.deptId = b.id ;

union 有去重功效

6. LEFT JOIN 去掉 相交的部分

select  * from  tbl_emp a  LEFT JOIN tbl_dept b on a.deptId = b.id 
where b.id is null;

7. RIGHT JOIN 去掉 相交的部分

select  * from  tbl_emp a  RIGHT JOIN tbl_dept b on a.deptId = b.id 
where a.deptId is null;

8.全连接 去掉中间相交的部分

select  * from  tbl_emp a  FULL JOIN  tbl_dept b on a.deptId = b.id 
where a.deptId is null or b.id is null;	

a表的独有 + b表的独有 =全连接去掉中间相交的部分

select  * from  tbl_emp a  LEFT JOIN tbl_dept b on a.deptId = b.id 
where b.id is null
union
select  * from  tbl_emp a  RIGHT JOIN tbl_dept b on a.deptId = b.id 
where a.deptId is null;