一张图告诉你常用的SQL连接，内连接、左外连接、右外连接、全连接

橙红色为结果集

附SQL语句：

-- 创建表并插入数据

CREATE TABLE person(id VARCHAR(10),NAME VARCHAR(20),parentid VARCHAR(10));
INSERT INTO person(id,NAME,parentid) VALUES('1','李四','1');
INSERT INTO person(id,NAME,parentid)  VALUES('2','王五','1');
INSERT INTO person(id,NAME,parentid)  VALUES('3','赵六','4');
SELECT * FROM person;
SELECT * FROM place;
CREATE TABLE place(id VARCHAR(10),NAME VARCHAR(20));
INSERT INTO place(id,NAME) VALUES('1','供销社');
INSERT INTO place(id,NAME) VALUES('2','服务部');
INSERT INTO place(id,NAME) VALUES('3','超市');

-- 内连接

SELECT * FROM person INNER JOIN place ON person.`parentid` = place.`id`;

-- 左外连接

SELECT * FROM person LEFT JOIN place ON person.`parentid` = place.`id`;

-- 左外连接代替内连接

SELECT * FROM person LEFT JOIN place ON person.`parentid` = place.`id` WHERE place.`id` IS NOT NULL;

-- Union代替全连接

SELECT * FROM person LEFT JOIN place ON person.`parentid` = place.`id` UNION
SELECT * FROM person RIGHT JOIN place ON person.`parentid` = place.`id`;
-- SELECT * FROM person FULL JOIN place ON person.`parentid` = place.`id`;
SELECT * FROM person,place;

PS：

  1、MySQL不支持全连接，所以我们通过SELECT * FROM person LEFT JOIN place ON person.`parentid` = place.`id` UNION
SELECT * FROM person RIGHT JOIN place ON person.`parentid` = place.`id`;来代替

  2、考虑性能，我们用左外连接来代替内连接