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)