SQL Server right Outer join usage

The right Outer join operator returns each row of the join that satisfies the second (bottom) input for each matching row entered with the first (top) input. In addition, it returns any row in the second input that does not have a matching row in the first input, that is, a NULL join. If there are no join predicates in the Argument column, each row is a matching row.

One, join (join)

By joining, you can retrieve data from two or more tables based on the logical relationship between each table. Joins indicate how the data in one table should be used to select rows from another table.

A join condition defines how two tables are associated in a query by using the following methods:

Specifies the columns to be used for joins in each table. A typical join condition specifies the foreign key in one table and the key associated with it in the other table. Specifies the logical operators (=, <>, and so on) to use when comparing the values of each column.

The following SQL statement shows that the left outer join between the titles table and the publishers table includes all the titles, even those with no publisher information: use pubs SELECT titles.title_id, Titles.title, Publishers.pub_name from the titles left OUTER JOIN publishers on titles.pub_id = publishers.pub_id

Instance

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> SELECT * 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        & nbsp title      averagesalary
----------------------------------
           1 developer           3000
          2 tester               4000
          3 Designer             5000
           4 programmer          6000

(4 rows affected)
1>
2>--right OUTER JOIN includes all of the information from the ' right '.
3>
4> SELECT e.id, e.name, J.title
5> from Employee e
6> right OUTER JOIN job J
7> &n bsp;            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>

Instance two right OUTER JOIN

Outer joins (Outer join) syntax

The syntax for the left OUTER join IS: SELECT column from table 1 in Outer]join table 2 on table 1 1= table 2. Column 2

The syntax for the right outer join is: SELECT select_list from table 1 right[outer]join table 2 on table 1 1= table 2. Column 2

The syntax for a full outer join (full outer joins) is: SELECT select_list from table 1 Full[outer] JOIN table 2 on table 1. Column 1= table 2. Column 2

Instance

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        & nbsp title      averagesalary www.111cn.net/6700s/
----------------------------------
          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>