SQL Server中的完全连接(full join) - 癫狂编程

SQL Server中的完全连接(full join)

一、建库和建表

create database scort
use scort
create table emp
(
    empno int primary key,
    ename nvarchar(10),
    sal int,
    deptno int
)
insert into emp values (7369,\'smith\',3000,20);
insert into emp values (7499,\'allen\',1500,50);
insert into emp values (7566,\'jones\',2000,30);
insert into emp values (7654,\'martin\',5000,10);
insert into emp values (7698,\'blake\',1800,30);
 
create table dept
(
    deptno int primary key,
    dname nvarchar(10),
    loc nvarchar(10)
)
insert into dept values (10,\'accounting\',\'new york\');
insert into dept values (20,\'research\',\'dallas\');
insert into dept values (30,\'sales\',\'chicago\');
insert into dept values (40,\'operations\',\'boston\');
 
create table salgrade
(
    grade int primary key,
    lostl int,
    hisal int
)
insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,6000);

二、完全连接的用法

结果集中包含三部分内容：

1. 两个表中匹配的所有行记录
2. 左表中那些在右表中找不到匹配的行的记录，这些记录的右边全为null
3. 右表中那些在左表中找不到四配的行的记录，这些记录的左边全为null

实例演示：

两张表连接：

select * from emp
    full join dept
    on emp.deptno=dept.deptno

三张表连接：

select * from emp "E"
    full join dept "D"
    on "E".deptno="D".deptno
    full join salgrade "S"
    on "E".sal>"S".lostl and "E".sal<"S".hisal