MySQL之join查询介绍

常用的Join查询:

Join最常见的就是内连接，左连接，外连接，交叉连接，笛卡尔积以及全连接。

最全的Join图：

上图全解释：

1.内连接：这两张表中间只有共有的部分。

SELECT * FROM TABLE A A INNER JOIN TABLE B B ON A.key = B.Key;

 

 

2.左全连接：两张表LEFT JOIN，A表的全部，A和B的共有加上A的独有。

SELECT * FROM TABLE A A LEFT JOIN TABLE B B ON A.key = B.key;

 

3.右全连接：两张表RIGHT JOIN，B表的全部，A和B的共有加上B的独有。

SELECT * FROM TABLE A A RIGHT JOIN TABLE B B ON A.key = B.key;

 

4.左连接：两张表LEFT JOIN，A表中只占它自己的部分，不与B表进行共享，那么B就是NULL。

SELECT * FROM TABLE A A LEFT JOIN TABLE B B ON A.Key = B.Key WHERE B.Key IS NULL;

 

5.右连接：两张表RIGHT JOIN，B表中只占它自己的部分，不与A表进行共享，那么A就是NULL。

SELECT * FROM TABLE A A RIGHT JOIN TABLE B B ON A.Key = B.Key WHERE A.Key IS NULL;

 

6.全连接：两张表FULL JOIN 全连接。

SELECT * FROM TABLE A A FULL JOIN TABLE B B ON A.Key = B.key;

 

7.左右连接：两张表没有共有部分，左右独立连接。

SELECT * FROM TABLE A A FULL OUTER JOIN TABLE B B ON A.Key = B.Key WHERE A.Key IS NULL ON B.Key IS NULL;

1.建表测试：

-- 1.tb_emp表。
DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `deptid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tb_emp_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb_emp`(name,deptid) VALUES ('jack', '1');
INSERT INTO `tb_emp`(name,deptid) VALUES ('tom', '1');
INSERT INTO `tb_emp`(name,deptid) VALUES ('tonny', '1');
INSERT INTO `tb_emp`(name,deptid) VALUES ('mary', '2');
INSERT INTO `tb_emp`(name,deptid) VALUES ('rose', '2');
INSERT INTO `tb_emp`(name,deptid) VALUES ('luffy', '3');
INSERT INTO `tb_emp`(name,deptid) VALUES ('outman', '14');

-- 2.tb_dept表。
DROP TABLE IF EXISTS `tb_dept`;
CREATE TABLE `tb_dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb_dept`(deptname) VALUES ('研发');
INSERT INTO `tb_dept`(deptname) VALUES ('测试');
INSERT INTO `tb_dept`(deptname) VALUES ('运维');
INSERT INTO `tb_dept`(deptname) VALUES ('经理');

2.查看部门表以及员工表：

 

3.Join图对应的Sql案例：

sql语句：

-- 1. 内连接
select * from tb_dept p INNER JOIN tb_emp e on p.id = e.deptid;
select * from tb_dept p JOIN tb_emp e on p.id = e.deptid;
-- 2. 左连接
select * from tb_dept p LEFT JOIN tb_emp e on p.id = e.deptid;
-- 3. 右连接
select * from tb_dept p RIGHT JOIN tb_emp e on p.id = e.deptid;
-- 4. 左外连接
select * from tb_dept p LEFT JOIN tb_emp e on p.id = e.deptid where e.id is null;
-- 5. 右外连接
select * from tb_dept p RIGHT JOIN tb_emp e on p.id = e.deptid where p.id is null;
-- 6. 全连接
select * from tb_dept p left JOIN tb_emp e on p.id=e.deptid 
UNION
select * from tb_dept p RIGHT JOIN tb_emp e ON p.id = e.deptid
-- 7. 左右连接
select * from tb_dept p left JOIN tb_emp e on p.id=e.deptid WHERE e.id is NULL
UNION
select * from tb_dept p RIGHT JOIN tb_emp e ON p.id = e.deptid WHERE p.id is null

1. inner join（内连接）

　　注：A表示左表，B表示右表，下同。

　　inner join：A、B共有，也就是交集。

　　

 

 

2. left join（左连接）

　　left jion：A独有+AB共有（交集）

　　

 

3.right join（右链接）

　　right join：B独有+AB共有（交集）

 

　　

 

4.A独有（左外连接）

注：参照left join，A独有只是将AB交集部分去掉。

5.B独有（右外连接）

注：参照right join，B独有只是将AB交集部分去掉。

 

6.AB全有（全连接）

 

 

 

由于mysql中不支持full outer join，所以这里通过union进行转换。AB并集：AB交集+A独有+B独有。

 

7.A、B独有并集（左右连接）

A、B独有并集，相当于A、B全有去掉AB的共有（交集）。