一、常见的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;