Right Outer Join

Right Outer Joins are the logical opposite of Left Outer Joins. Right Outer Joins return all records from the 'right' table even if there is no record match in the 'left' table.


Look at this:
SELECT <COLUMNS>
FROM <TABLE1>RIGHT JOIN <TABLE2>
ON <TABLE1>.<COLUMN> = <TABLE2>.<COLUMN>

In the above sample, <TABLE1> is the left table and <TABLE2> is the right table.

In this line:


     ON <TABLE1>.<COLUMN> = <TABLE2>.<COLUMN>


<COLUMN> is the name of the column that is similar, the column that will be used for matching.


Note: RIGHT JOIN is the same as RIGHT OUTER JOIN


Here is an example:

1. USE master;
2. GO
3. -- Create our first table variable to hold user accounts.
4. DECLARE @UserAccounts TABLE
5. (
6.     acountID INT
7.     , firstName VARCHAR(50)
8.     , lastName VARCHAR(50)
9. );
10. -- Create our second table variable to hold our user account passwords.
11. DECLARE @Passwords TABLE
12. (
13.     acountID INT
14.     , [password] VARCHAR(50)
15. );
16. -- Insert some records into our UserAccounts table variable.
17. INSERT INTO @UserAccounts
18. VALUES (1, 'John', 'Doe');
19. INSERT INTO @UserAccounts
20. VALUES (2, 'Jane', 'Doe');
21. INSERT INTO @UserAccounts
22. VALUES (3, 'Jim', 'Doe');
23. INSERT INTO @UserAccounts
24. VALUES (4, 'Jimmy', 'Doe');
25. INSERT INTO @UserAccounts
26. VALUES (5, 'Fonda', 'Doe');
27. -- Insert some records into our Passwords table variable.
28. INSERT INTO @Passwords
29. VALUES (1, 'password');
30. INSERT INTO @Passwords
31. VALUES (2, 'jane32');
32. INSERT INTO @Passwords
33. VALUES (3, 'Password!');
34. INSERT INTO @Passwords
35. VALUES (10, 'bad password!');
36. -- Right outer join between two table variables.
37. SELECT U.firstName, U.lastName, P.[password]
38. FROM @UserAccounts AS U RIGHT OUTER JOIN @Passwords AS P
39. ON U.acountID = P.acountID;

The results of this query are:


(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
firstName                                          lastName                                           password
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
John                                               Doe                                                password
Jane                                               Doe                                                jane32
Jim                                                Doe                                                Password!
NULL                                               NULL                                               bad password!

(4 row(s) affected)