MySQL 左连接，右连接，内连接，UNION连接 ， 全连接图解，用法代码理解

       MySQL 左连接，右连接，内连接，UNION连接 ， 全连接图解，用法代码理解

 

一、图解 （引用 ）

1.INNER JOIN（内连接）

 

SELECT <select_list>
FROM Table_A A
INNER JOIN Table_B B ON A.Key = B.Key

 

2.LEFT JOIN（左连接）

 

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B ON A.Key = B.Key

3.RIGHT JOIN（右连接）

 

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B ON A.Key = B.Key

4.OUTER JOIN（外连接）

 

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B ON A.Key = B.Key

补充： MySQL不支持 full join , 使用 union , union all 进行操作。

 

5.LEFT JOIN EXCLUDING INNER JOIN（左连接-内连接）

 

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B ON A.Key = B.Key
WHERE B.Key IS NULL

6.RIGHT JOIN EXCLUDING INNER JOIN（右连接-内连接）

 

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B ON A.Key = B.Key
WHERE A.Key IS NULL

7.OUTER JOIN EXCLUDING INNER JOIN（外连接-内连接）

 

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

 

8. 总结：左，右，内，外（全）连接，左--内，右--内，外--内 连接。 共7种方式。

 

二、client表 和 image 表创建

a. 创建 client 表

DROP TABLE IF EXISTS `client`;
CREATE TABLE `client` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '客户id',
  `name` varchar(255) DEFAULT NULL COMMENT '客户名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of client
-- ----------------------------
INSERT INTO `client` VALUES ('2', '小八2222');
INSERT INTO `client` VALUES ('3', '小明3tttff');
INSERT INTO `client` VALUES ('4', '小明4');
INSERT INTO `client` VALUES ('5', '小米6677');

b. 创建 image 表，中client_id 字段和 client 表中 id 关联

DROP TABLE IF EXISTS `image`;
CREATE TABLE `image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` varchar(255) DEFAULT NULL,
  `content` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of image
-- ----------------------------
INSERT INTO `image` VALUES ('1', '2', '222');
INSERT INTO `image` VALUES ('2', '3', '333');
INSERT INTO `image` VALUES ('3', '6', '666');

 

三、代码理解

1、 查询 client 表数据

-- 查询 client 表数据
SELECT a.* from client a ;

 

 

2、查询image表数据

-- 查询image表数据
SELECT a.* from image a ;

 

3、client 表 左连接查询 LEFT JOIN

-- client 表 左连接查询 LEFT JOIN
SELECT a.id,a.`name`,b.id,b.content 
from client a
LEFT JOIN image b on b.client_id= a.id ;

 

4、client 表 右连接查询 RIGHT JOIN

-- client 表 右连接查询 RIGHT JOIN
SELECT a.id,a.`name`,b.id,b.client_id ,b.content 
from client a
RIGHT JOIN image b on b.client_id= a.id ;

5、client 表 内连接查询 INNER JOIN

-- client 表 内连接查询 INNER JOIN
SELECT a.id,a.`name`,b.id,b.client_id,b.content 
from client a
-- INNER JOIN image b on b.client_id= a.id ;
JOIN image b on b.client_id= a.id ; -- 内链接 INNER 关键字可以省略

 

5.2、client 表 内连接查询 INNER JOIN ， 省略 INNER JOIN 关键字

SELECT a.id,a.`name`,b.id,b.client_id,b.content 
from client a , image b where a.id = b.client_id ; -- 内链接 INNER JOIN 关键字都可以省略

 

6.1 、client,image 表全连接查询

-- client,image 表全连接查询 
-- error: MySQL 不支持全连接full join 查询 
SELECT a.id,a.`name`,b.id,b.content 
from client a
FULL OUTER JOIN  image b on b.client_id = a.id ;

 

 

四、MySQL使用 UNION ， UNION ALL 表示全连接

1、client,image 使用 union 连接

-- client,image 使用 union 连接
SELECT  a.id,a.`name`,b.id,b.content 
from client a
LEFT JOIN image b on b.client_id = a.id
UNION
SELECT  a.id,a.`name`,b.id,b.content 
from client a
RIGHT JOIN image b on b.client_id = a.id;

 

2、client,image 使用 union all 连接

-- client,image 使用 union all 连接 
SELECT  a.id,a.`name`,b.id,b.content 
from client a
LEFT JOIN image b on b.client_id = a.id
UNION all
SELECT  a.id,a.`name`,b.id,b.content 
from client a
RIGHT JOIN image b on b.client_id = a.id;

 

3、总结： UNION 连接查询获取的去除重复后的数据集， UNION ALL 连接获取的是全部数据集，没有去除重复操作。

 

五、UNION 和 UNION ALL 的区别

1、MySQL中用 UNION 把多个 SELECT 语句结果组合到一个结果集中。

2、语法格式

select * from tableA 
UNION [ALL]
select * from tableB 
UNION [ALL]
... 

3、区别：从 【四】中代码演示结果可知 UNION 和 UNION ALL 区别：

• 使用 UNION 时，会去掉重复记录。
• 使用 UNION ALL 时，会返回多个结果集获取到的全部记录，没有去重复。
• UNION 由于会有去重复记录的操作，故效率低于 UNION ALL 。

 

参考资料：https://mp.weixin.qq.com/s/peuNnWtuUFPftBRuODYQ3Q

                  http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins