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)

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)

Left Outer Join

Left Joins are cool in that fact that they return all records from the 'left' table and all the records from the 'right' table that have a match with a record from the 'left' table. If there is no match found, the query will place a 'null' value where there was no match found.

Left table? Right table? What?! If the idea of a left and right table is confusing to you, do not be discouraged. It is not too horrible to figure out. The left table is generally the table listed first, and you guessed it, the right table is generally the table listed second.

Look at this:
SELECT <COLUMNS>
FROM <TABLE1>LEFT 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: LEFT JOIN is the same as LEFT OUTER JOIN


And here is an example that you can run yourself to see the results:

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

This query will produce the following 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)
firstName                                          lastName                                           password
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
John                                               Doe                                                password
Jane                                               Doe                                                jane32
Jim                                                Doe                                                Password!
Jimmy                                              Doe                                                NULL
Fonda                                              Doe                                                NULL

(5 row(s) affected)

Table Variables and Inner Joins

When we create and use table variables, we sometimes need to user inner joins on table variables. Well, thankfully, this is the same as joining two regular tables:

1. USE master;
2. GO
4. -- Create our first table variable to hold user accounts.
5. DECLARE @UserAccounts TABLE
6. (
7.     acountID INT
8.     , firstName VARCHAR(50)
9.     , lastName VARCHAR(50)
10. );
12. -- Create our second table variable to hold our user account passwords.
13. DECLARE @Passwords TABLE
14. (
15.     acountID INT
16.     , [password] VARCHAR(50)
17. );
19. -- Insert some records into our UserAccounts table variable.
20. INSERT INTO @UserAccounts
21. VALUES (1, 'John', 'Doe');
22. INSERT INTO @UserAccounts
23. VALUES (2, 'Jane', 'Doe');
24. INSERT INTO @UserAccounts
25. VALUES (3, 'Jim', '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!');
35. -- Inner join between two table variables.
37. SELECT U.firstName, U.lastName, P.[password]
38. FROM @UserAccounts AS U
39. INNER JOIN @Passwords AS P
40. ON U.acountID = P.acountID;

The output of this query should be:


(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!

(3 row(s) affected)

Updating Records And Table Variables

In a previous post, located here, we talked about inserting records into table variables. Then in this post we talked about deleting records from a table variable. Now I will illustrate updating records within a table variable. It is the same as updating records of normal tables, using an "UPDATE" statement:

1. USE master;
2. GO
3. -- The creation of our Table Variable.
4. DECLARE @UserAccounts TABLE
5. (
6.     firstName VARCHAR(50)
7.     , lastName VARCHAR(50)
8. );
10. -- Inserting 3 records into our Table Variable.
11. INSERT INTO @UserAccounts
12. VALUES ('John', 'Doe');
14. INSERT INTO @UserAccounts
15. VALUES ('Jane', 'Doe');
17. INSERT INTO @UserAccounts
18. VALUES ('Jim', 'Doe');
20. -- Update a record from our Table Variable.
21. UPDATE @UserAccounts
22. SET firstName = 'This is an unrealistic name.'
23. WHERE firstName = 'Jim';
25. -- Selecting the records from our Table Variable.
26. SELECT * FROM @UserAccounts;

The result of this query should be:


(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
firstName                                          lastName
-------------------------------------------------- --------------------------------------------------
John                                               Doe
Jane                                               Doe
This is an unrealistic name.                       Doe

(3 row(s) affected)