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);
二、完全连接的用法
结果集中包含三部分内容:
- 两个表中匹配的所有行记录
- 左表中那些在右表中找不到匹配的行的记录,这些记录的右边全为null
- 右表中那些在左表中找不到四配的行的记录,这些记录的左边全为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
好的代码像粥一样,都是用时间熬出来的