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