先看一张图,像不像数学中的集合,并集,交集,差集啥的。
下面我就用具体的例子来演示上图的7种情况:
新建两张表:t_emp和t_dept(员工表和部门表),这里约定一下,员工表t_emp代表图中A表,部门表t_dept代表图中B表。
创建表并插入数据:
DROP TABLE IF EXISTS `t_emp`;
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT "主键",
`dept_code` VARCHAR(40) DEFAULT NULL COMMENT "部门代码",
`dept_name` VARCHAR(40) DEFAULT NULL COMMENT "部门名称",
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT "主键",
`name` VARCHAR(20) DEFAULT NULL COMMENT "员工名称",
`age` INT(3) DEFAULT NULL COMMENT "年龄",
`dept_id` INT(11) DEFAULT NULL COMMENT "部门表id",
PRIMARY KEY (`id`),
CONSTRAINT `fk_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;
INSERT INTO t_dept(dept_code, dept_name) VALUES('ceo','总裁部门');
INSERT INTO t_dept(dept_code, dept_name) VALUES('development','开发部门');
INSERT INTO t_dept(dept_code, dept_name) VALUES('test','测试部门');
INSERT INTO t_emp(NAME,age,dept_id) VALUES('小明', 25, 2);
INSERT INTO t_emp(NAME,age,dept_id) VALUES('小红', 23, 3);
INSERT INTO t_emp(NAME,age,dept_id) VALUES('小王', 35, null);
部门表:
员工表:
从上面两张图可以看出 总裁部门中没有员工,小王没有部门。
左外连接
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name
FROM t_emp a
LEFT JOIN t_dept b
on a.dept_id = b.id;
以左边表(t_emp )为准,小王没有部门,右边表字段用null来补充。
左外连接(获取t_emp 表独有的)
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name
FROM t_emp a
LEFT JOIN t_dept b
on a.dept_id = b.id
WHERE a.dept_id is null;
右外连接
右外连接,以右边表为准,左表中没有的用null补充
右外连接(获取t_dept 表独有的)
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name
FROM t_emp a
RIGHT JOIN t_dept b
on a.dept_id = b.id
WHERE a.dept_id is null;
内连接
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name
FROM t_emp a
INNER JOIN t_dept b
on a.dept_id = b.id
获取两张表共有的
满外连接
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name
FROM t_emp a
FULL OUTER JOIN t_dept b
on a.dept_id = b.id
执行后发现报错了:
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name
FROM t_emp a
FULL OUTER JOIN t_dept b
on a.dept_id = b.id
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN t_dept b
on a.dept_id = b.id' at line 3
> 时间: 0.001s
这是因为mysq 不支持 FULL OUTER JOIN
换一种方式:使用UNION
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name
FROM t_emp a
LEFT JOIN t_dept b
on a.dept_id = b.id
UNION
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name
FROM t_emp a
RIGHT JOIN t_dept b
on a.dept_id = b.id
因为要联合的缘故,不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的一致。
满外连接(两表独有)
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name
FROM t_emp a
LEFT JOIN t_dept b
on a.dept_id = b.id
WHERE a.dept_id is null
UNION
SELECT a.name, a.age, a.dept_id, b.dept_code, b.dept_name
FROM t_emp a
RIGHT JOIN t_dept b
on a.dept_id = b.id
WHERE a.dept_id is null;
评论