当前位置:Gxlcms > mysql > sqlserverRightOuterJoin用法

sqlserverRightOuterJoin用法

时间:2021-07-01 10:21:17 帮助过:83人阅读

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


一、联接( 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>

人气教程排行