橙红色为结果集\

附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、考虑性能,我们用左外连接来代替内连接