MySQL的7种JOIN

-- 创建数据库
CREATE DATABASE emp;

-- 创建部门表
CREATE TABLE emp.dept (
    id INT(11) NOT NULL AUTO_INCREMENT,
    deptName VARCHAR(30) DEFAULT NULL,
    iocAdd VARCHAR(40) DEFAULT NULL,
    PRIMARY KEY (id)
) DEFAULT CHARSET = utf8;

-- 创建员工表
CREATE TABLE emp.emp (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) DEFAULT NULL,
    deptId INT(11) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY fk_dept_id (deptId)
) DEFAULT CHARSET = utf8;

-- 插入部门数据
INSERT INTO emp.dept (deptName, iocAdd) VALUES ('RD', 11);
INSERT INTO emp.dept (deptName, iocAdd) VALUES ('HR', 12);
INSERT INTO emp.dept (deptName, iocAdd) VALUES ('MK', 13);
INSERT INTO emp.dept (deptName, iocAdd) VALUES ('MIS', 14);
INSERT INTO emp.dept (deptName, iocAdd) VALUES ('FD', 15);

-- 插入员工数据
INSERT INTO emp.emp(name, deptId) VALUES ('z3', 1);
INSERT INTO emp.emp(name, deptId) VALUES ('z4', 1);
INSERT INTO emp.emp(name, deptId) VALUES ('z5', 1);
INSERT INTO emp.emp(name, deptId) VALUES ('w5', 2);
INSERT INTO emp.emp(name, deptId) VALUES ('w6', 2);
INSERT INTO emp.emp(name, deptId) VALUES ('s7', 3);
INSERT INTO emp.emp(name, deptId) VALUES ('s8', 4);
INSERT INTO emp.emp(name, deptId) VALUES ('s9', 51);

-- 查询所有员工
SELECT * FROM emp.emp;

-- 查询所有部门
SELECT * FROM emp.dept;

-- 笛卡尔积
SELECT * FROM emp.dept, emp.emp;

-- 内连接
SELECT * FROM emp INNER JOIN dept ON emp.deptId = dept.id;

-- 左连接
SELECT * FROM emp LEFT JOIN dept ON emp.deptId = dept.id;

-- 右连接
SELECT * FROM emp RIGHT JOIN dept ON emp.deptId = dept.id;

-- 左连接查找空值
SELECT * FROM emp LEFT JOIN dept ON emp.deptId = dept.id WHERE dept.id IS NULL;

-- 右连接查找空值
SELECT * FROM emp RIGHT JOIN dept ON emp.deptId = dept.id WHERE emp.id IS NULL;

-- 模拟全外连接
SELECT * FROM emp LEFT JOIN dept ON emp.deptId = dept.id
UNION
SELECT * FROM emp RIGHT JOIN dept ON emp.deptId = dept.id;

-- 替代的全外连接模拟
SELECT * FROM emp LEFT JOIN dept ON emp.deptId = dept.id WHERE dept.id IS NULL
UNION
SELECT * FROM emp.RIGHT JOIN dept ON emp.deptId = dept.id WHERE emp.id IS NULL;