一张图告诉你常用的SQL连接

页面导航:首页 > 数据库 > MsSql > 一张图告诉你常用的SQL连接

一张图告诉你常用的SQL连接

来源: 作者: 时间:2016-01-20 09:48 【

橙红色为结果集附SQL语句:-- 创建表并插入数据 CREATE TABLE person(id VARCHAR(10),NAME VARCHAR(20),parentid VARCHAR(10));INSERT INTO person(id,NAME,parentid) VALUES( 1 , 李四 ,

橙红色为结果集\

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

Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<