【MySQL】第四篇：MySQL的Join

一、常见的Join查询图

七种SQL Join：

二、Join示例

数据

CREATE TABLE `t_dept` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`deptName` VARCHAR(30) DEFAULT NULL,
	`address` VARCHAR(40) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARSET = utf8;

CREATE TABLE `t_emp` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) DEFAULT NULL,
	`age` INT(3) DEFAULT NULL,
	`deptId` INT(11) DEFAULT NULL,
	empno INT NOT NULL,
	PRIMARY KEY (`id`),
	KEY `idx_dept_id` (`deptId`)
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARSET = utf8;


INSERT INTO t_dept (deptName, address)
VALUES ('华山', '华山');

INSERT INTO t_dept (deptName, address)
VALUES ('丐帮', '洛阳');

INSERT INTO t_dept (deptName, address)
VALUES ('峨眉', '峨眉山');

INSERT INTO t_dept (deptName, address)
VALUES ('武当', '武当山');

INSERT INTO t_dept (deptName, address)
VALUES ('明教', '光明顶');

INSERT INTO t_dept (deptName, address)
VALUES ('少林', '少林寺');

INSERT INTO t_emp (NAME, age, deptId, empno)
VALUES ('风清扬', 90, 1, 100001);

INSERT INTO t_emp (NAME, age, deptId, empno)
VALUES ('岳不群', 50, 1, 100002);

INSERT INTO t_emp (NAME, age, deptId, empno)
VALUES ('令狐冲', 24, 1, 100003);

INSERT INTO t_emp (NAME, age, deptId, empno)
VALUES ('洪七公', 70, 2, 100004);

INSERT INTO t_emp (NAME, age, deptId, empno)
VALUES ('乔峰', 35, 2, 100005);

INSERT INTO t_emp (NAME, age, deptId, empno)
VALUES ('灭绝师太', 70, 3, 100006);

INSERT INTO t_emp (NAME, age, deptId, empno)
VALUES ('周芷若', 20, 3, 100007);

INSERT INTO t_emp (NAME, age, deptId, empno)
VALUES ('张三丰', 100, 4, 100008);

INSERT INTO t_emp (NAME, age, deptId, empno)
VALUES ('张无忌', 25, 5, 100009);

INSERT INTO t_emp (NAME, age, deptId, empno)
VALUES ('韦小宝', 18, NULL, 100010);

join示例

-- 列出所有门派
SELECT * FROM t_dept;

-- 列出所有无门派人士
SELECT * FROM t_emp WHERE deptId IS NULL;

-- 所有有门派人员的信息(要求显示门派名称)：内连接
SELECT e.name, e.age, e.empno, d.deptName, d.address FROM t_emp e
INNER JOIN t_dept d ON e.deptId = d.id;

-- 列出所有人员及其门派信息：左连接
SELECT e.name, e.age, e.empno, d.deptName, d.address FROM t_emp e
LEFT JOIN t_dept d ON e.deptId = d.id;

-- 列出所有的门派及其人员信息：右连接
SELECT e.name, e.age, e.empno, d.deptName, d.address FROM t_emp e
RIGHT JOIN t_dept d ON e.deptId = d.id;

-- 所有无人的门派：右连接并且deptId为null
SELECT d.deptName, d.address FROM t_emp e
RIGHT JOIN t_dept d ON e.deptId = d.id
WHERE e.deptId IS NULL;

-- 所有人员和门派的对应关系：全连接
-- select * from t_emp e full outer JOIN t_dept d ON e.`deptId`=d.`id`;  企业版可支持，但等价于下面的语句
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.`deptId` = d.`id`
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.`deptId` = d.`id`;

-- 所有没有入门派的人员和没人入的门派
-- select * from t_emp e full outer JOIN t_dept d ON e.`deptId`=d.`id` where e.deptId is null and d.id is null; 企业版可支持，但等价于下面的语句
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.`deptId` = d.`id` WHERE d.id IS NULL
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.`deptId` = d.`id` WHERE e.deptId IS NULL;