SQL92用+解决外连接,但在SQL92里面没有满外连接。
SQL99使用JOIN ON 语句来解决外连接。
Oracle对SQL92支持较好,也能使用SQL99,而MySQL只能用SQL99进行外连接
但MySQL不支持SQL99使用FULL JION进行满外连接。
UNION操作符
UNION操作符返回两个查询的结果集的并集,去除重复记录
UNION ALL操作符
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。
7种JOIN的实现
内连接
查询A图和B图里面都有的数据
SELECT employee_id,department_name FROM employee e JOIN departments d ON e.'employee_id'=d.'department_name';
![]()
左外连接
查询A图里面有,但是B图里为NULL。
和A图和B图里面都有的记录。
左上图
SELECT employee_id,department_name FROM employee e LEFT JOIN departments d ON e.'employee_id'=d.'department_name';
右外连接
查询B图里面有,但是A图里为NULL。
和A图和B图里面都有的记录。
SELECT employee_id,department_name FROM employee e RIGHT JOIN departments d ON e.'employee_id'=d.'department_name';
![]()
A⊕ B
查询只有A图里面有,B图里面的数据为NULL的记录
SELECT employee_id,department_name FROM employee e LEFT JOIN departments d ON e.'employee_id'=d.'employee_id' WHERE d.'department_name' IS NULL;
B⊕ A
查询只有B图里面有,A图里面的数据为NULL的记录
SELECT employee_id,department_name FROM employee e RIGHT JOIN departments d ON e.'employee_id'=d.'employee_id' WHERE e.'department_name' IS NULL;
![]()
满外连接
查询两个表中的A与B匹配,A不匹配B,B不匹配A的数据
(MYSQL,SQL99)方法一:左外连接(右外连接) 和 B⊕ A(A⊕ B)两部分使用UNION ALL语句
SELECT employee_id,department_name FROM employee e LEFT JOIN departments d ON e.'employee_id'=d.'department_name'; UNION ALL SELECT employee_id,department_name FROM employee e RIGHT JOIN departments d ON e.'employee_id'=d.'employee_id' WHERE e.'department_name' IS NULL;
(Oracle,SQL99)方法二:FULL JOIN语句
SELECT employee_id,department_name FROM employee e FULL JOIN departments d ON e.'employee_id'=d.'department_name';
(A⊕ B) ∪ (B⊕ A)
SELECT employee_id,department_name
FROM employee e LEFT JOIN departments d
ON e.'employee_id'=d.'employee_id'
WHERE d.'department_name' IS NULL;
UNION ALL
SELECT employee_id,department_name
FROM employee e RIGHT JOIN departments d
ON e.'employee_id'=d.'employee_id'
WHERE e.'department_name' IS NULL;