一、联接（ join）
通过联接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。联接表示应如何使用一个表中的数据来选择另一个表中的行。
联接条件通过以下方法定义两个表在查询中的关联方式：
指定每个表中要用于联接的列。典型的联接条件在一个表中指定外键，在另一个表中指定与其关联的键。指定比较各列的值时要使用的逻辑运算符（=、 等）。
下面的 sql 语句说明 titles 表和 publishers 表之间的左向外联接包括所有的书名，甚至包括那些没有出版商信息的书名： use pubs select titles.title_id, titles.title, publishers.pub_name from titles left outer join publishers on titles.pub_id = publishers.pub_id
实例
1> create table employee(
2> id int,
3> name nvarchar (10),
4> salary int )
5> go
1>
2> create table job(
3> id int,
4> title nvarchar (10),
5> averagesalary int)
6> go
1>
2>
3> insert into employee (id, name, salary) values (1, 'jason', 1234)
4> go
(1 rows affected)
1> insert into employee (id, name, salary) values (2, 'robert', 4321)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (3, 'celia', 5432)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (4, 'linda', 3456)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (5, 'david', 7654)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (6, 'james', 4567)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (7, 'alison', 8744)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (8, 'chris', 9875)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (9, 'mary', 2345)
2> go
(1 rows affected)
1>
2> insert into job(id, title, averagesalary) values(1,'developer',3000)
3> go
(1 rows affected)
1> insert into job(id, title, averagesalary) values(2,'tester', 4000)
2> go
(1 rows affected)
1> insert into job(id, title, averagesalary) values(3,'designer', 5000)
2> go
(1 rows affected)
1> insert into job(id, title, averagesalary) values(4,'programmer', 6000)
2> go
(1 rows affected)
1>
2>
3>
4> * from employee;
5> go
id name salary
----------- ---------- -----------
1 jason 1234
2 robert 4321
3 celia 5432
4 linda 3456
5 david 7654
6 james 4567
7 alison 8744
8 chris 9875
9 mary 2345
(9 rows affected)
1> select * from job;
2> go
id title averagesalary
----------- ---------- -------------
1 developer 3000
2 tester 4000
3 designer 5000
4 programmer 6000
(4 rows affected)
1>
2> -- right outer join includes all the information from the table on the right.
3>
4> select e.id, e.name, j.title
5> from employee e
6> right outer join job j
7> on e.id = j.id
8> go
id name title
----------- ---------- ----------
1 jason developer
2 robert tester
3 celia designer
4 linda programmer
(4 rows affected)
1>
2>
3> drop table employee;
4> drop table job;
5> go
1>
2>
实例二 right outer join
外连接（outer join）语法
左外连接的语法为： select 列 from 表1 left [outer]join 表2 on 表1.列1=表2.列2
右外连接的语法为： select select_list from 表1 right[outer]join 表2 on 表1.列1=表2.列2
全外连接(完整外部联接)的语法为： select select_list from 表1 full[outer] join 表2 on 表1.列1=表2.列2
实例
1> create table employee(
2> id int,
3> name nvarchar (10),
4> salary int )
5> go
1>
2> create table job(
3> id int,
4> title nvarchar (10),
5> averagesalary int)
6> go
1>
2>
3> insert into employee (id, name, salary) values (1, 'jason', 1234)
4> go
(1 rows affected)
1> insert into employee (id, name, salary) values (2, 'robert', 4321)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (3, 'celia', 5432)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (4, 'linda', 3456)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (5, 'david', 7654)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (6, 'james', 4567)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (7, 'alison', 8744)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (8, 'chris', 9875)
2> go
(1 rows affected)
1> insert into employee (id, name, salary) values (9, 'mary', 2345)
2> go
(1 rows affected)
1>
2> insert into job(id, title, averagesalary) values(1,'developer',3000)
3> go
(1 rows affected)
1> insert into job(id, title, averagesalary) values(2,'tester', 4000)
2> go
(1 rows affected)
1> insert into job(id, title, averagesalary) values(3,'designer', 5000)
2> go
(1 rows affected)
1> insert into job(id, title, averagesalary) values(4,'programmer', 6000)
2> go
(1 rows affected)
1>
2>
3> select * from employee;
4> go
id name salary
----------- ---------- -----------
1 jason 1234
2 robert 4321
3 celia 5432
4 linda 3456
5 david 7654
6 james 4567
7 alison 8744
8 chris 9875
9 mary 2345
(9 rows affected)
1> select * from job;
2> go
id title averagesalary
----------- ---------- -------------
1 developer 3000
2 tester 4000
3 designer 5000
4 programmer 6000
(4 rows affected)
1>
2>
3> select e.name from employee e
4> right outer join job j
5> on e.id = j.id
6> where e.id is not null
7> go
name
----------
jason
robert
celia
linda
(4 rows affected)
1>
2>
3> drop table employee;
4> drop table job;
5> go
1>