MySQL的7种JOIN，图片加SQL

7种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 DEFAULT 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,
	PRIMARY KEY ( `id` ),
	KEY `fk_dept_id` ( `deptId` ),
	CONSTRAINT `fk_dept_id` FOREIGN KEY ( `deptId` ) REFERENCES `t_dept` ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT 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 )
VALUES
	( '风清扬', 90, 1 );
INSERT INTO t_emp ( NAME, age, deptId )
VALUES
	( '岳不群', 50, 1 );
INSERT INTO t_emp ( NAME, age, deptId )
VALUES
	( '令狐冲', 24, 1 );
INSERT INTO t_emp ( NAME, age, deptId )
VALUES
	( '洪七公', 70, 2 );
INSERT INTO t_emp ( NAME, age, deptId )
VALUES
	( '乔峰', 35, 2 );
INSERT INTO t_emp ( NAME, age, deptId )
VALUES
	( '灭绝师太', 70, 3 );
INSERT INTO t_emp ( NAME, age, deptId )
VALUES
	( '周芷若', 20, 3 );
INSERT INTO t_emp ( NAME, age, deptId )
VALUES
	( '张三丰', 100, 4 );
INSERT INTO t_emp ( NAME, age, deptId )
VALUES
	( '张无忌', 25, 5 );
INSERT INTO t_emp ( NAME, age, deptId )
VALUES
	( '韦小宝', 18, NULL );

执行SQL

下面是7种序号和图一一对应。

#1内连接 A、B两表共有
SELECT * 
FROM t_dept
	INNER JOIN t_emp ON t_dept.id = t_emp.deptId;
#2左外连接 A、B两表共有+A的独有。左边表全部的列都会显示，如果右边的表没有对应的值，则显示null
SELECT *
FROM t_dept
	LEFT JOIN t_emp ON t_dept.id = t_emp.deptId;
#3右外连接 A、B两表共有+B的独有。右边表全部的列都会显示，如果左边的表没有对应的值，则显示null
SELECT *
FROM t_dept
	RIGHT JOIN t_emp ON t_dept.id = t_emp.deptId;
#4左连接 A的独有。只显示左边表的独立部分，即右边的表没有与左边表对应的列
SELECT *
FROM t_dept
	LEFT JOIN t_emp ON t_dept.id = t_emp.deptId
WHERE t_emp.deptId IS NULL;
#5右连接 B的独有。与左连接相反
SELECT * 
FROM t_dept
	RIGHT JOIN t_emp ON t_dept.id = t_emp.deptId
WHERE t_dept.id IS NULL;
#6全连接 AB全有。MySQL不支持FULL JOIN，(本人测试，用FULL JOIN 而不加ON条件，是笛卡尔积，加ON报错)
SELECT *
FROM t_dept
	LEFT JOIN t_emp ON t_dept.id = t_emp.deptId
UNION
SELECT *
FROM t_dept
	RIGHT JOIN t_emp ON t_dept.id = t_emp.deptId;
#7全外连接 A的独有+B的独有
SELECT *
FROM t_dept
	LEFT JOIN t_emp ON t_dept.id = t_emp.deptId
WHERE t_emp.deptId IS NULL
UNION
SELECT *
FROM t_dept
	RIGHT JOIN t_emp ON t_dept.id = t_emp.deptId
WHERE t_dept.id IS NULL