Full Outer Join

When you query two tables using Right Outer Joins, the query returns all records from the right table and records from the left table that match the records from the left. And when using Left Outer Joins, the query return all records from the left table and records all records from the right table that has a match in the left table.

A basic Full Outer Join return all records from all tables, regardless of any matches. If there is not match, the join will return a 'null' value.

Here is an example of a full outer join:

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!');
37. -- Full outer join between two table variables.
38. SELECT U.firstName, U.lastName, P.[password]
39. FROM @UserAccounts AS U FULL OUTER JOIN @Passwords AS P
40. ON U.acountID = P.acountID;

This query returns this result:

(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!
Jimmy                                              Doe                                                NULL
Fonda                                              Doe                                                NULL
NULL                                               NULL                                               bad password!

(6 row(s) affected)