Right Outer Join
运算符返回满足第二个输入与第一个输入的每个匹配行的联接的每行。此外,它还返回第二个输入中在第一个输入中没有匹配行的任何行,即与
NULL 联接。如果 Argument 列内不存在任何联接谓词,则每行都是一个匹配行。

一、联接

通过联接,可以根据各个表之间的逻辑关系从两个或多个表中检索数据。联接表示应如何使用一个表中的数据来选择另一个表中的行。

联接条件通过以下方法定义两个表在查询中的关联方式:

指定每个表中要用于联接的列。典型的联接条件在一个表中指定外键,在另一个表中指定与其关联的键。指定比较各列的值时要使用的逻辑运算符。

下面的 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
GO12 create table job(3 ID int,4 title nvarchar (10),5 averageSalary
int)6 GO123 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)12 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)1234 select * from employee;5 GOID 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 GOID title
averageSalary———– ———- ————- 1 Developer 3000 2
Tester 4000 3 Designer 5000 4 Programmer 6000

(4 rows affected)12 — RIGHT OUTER JOIN includes all the information
from the table on the right.34 SELECT e.id, e.name, j.title5 FROM
Employee e6 RIGHT OUTER JOIN job j7 ON e.id = j.id8 GOid name
title———– ———- ———- 1 Jason Developer 2 Robert
Tester 3 Celia Designer 4 Linda Programmer

(4 rows affected)123 drop table employee;4 drop table job;5 GO12

实例二 RIGHT 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
GO12 create table job(3 ID int,4 title nvarchar (10),5 averageSalary
int)6 GO123 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)12 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)123 select * from employee;4 GOID 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 GOID title averageSalary
———- ————- 1 Developer 3000 2 Tester 4000 3 Designer
5000 4 Programmer 6000

(4 rows affected)123 SELECT e.name FROM Employee e4 RIGHT OUTER JOIN
job j5 ON e.id = j.id6 WHERE e.id IS NOT NULL7
GOname———-JasonRobertCeliaLinda

(4 rows affected)123 drop table employee;4 drop table job;5 GO1

网站地图xml地图