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)

Deleting Records And Table Variables

In this post I talked about the creation of a table variable and then inserting records into our table variable. At times we will need to remove records from our table variable, and the way to do this is just like removing records from normal tables using the "DELTE" 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. -- Delete a record from our Table Variable.
21. DELETE FROM @UserAccounts
22. WHERE firstName = 'John';
24. -- Selecting the records from our Table Variable.
25. SELECT * FROM @UserAccounts;

The output of this query should be:



(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
firstName                                          lastName
-------------------------------------------------- --------------------------------------------------
Jane                                               Doe
Jim                                                Doe

(2 row(s) affected)

Inserting Records With Table Variables

In a previous post, located here, I talked about using table variables with SQL Server. This post will show the basic insertion of records into a table variable we will created called "UserAccounts". The fields will be "firstName" and "lastName". Then, we will select all the records from our table variable.

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. -- Selecting the records from our Table Variable.
21. SELECT * FROM @UserAccounts;

The output of running this query should be:



(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

firstName                                          lastName
-------------------------------------------------- --------------------------------------------------
John                                               Doe
Jane                                               Doe
Jim                                                Doe

(3 row(s) affected)

Resources

Check back to this post often as it will be updated and improved over time.


Microsoft


http://msdn.microsoft.com/en-us/ - The Microsoft MSDN website. Provides articles, whitepapers, interviews, and sample code for software developers using Microsoft products.


http://www.asp.net/ - The Official ASP.NET website. Official site maintained by Microsoft with links to information, IBuySpy and other community sites and resources


http://www.microsoft.com/net/ - Microsoft .NET Framework website.


http://msdn.microsoft.com/en-us/vbasic/default - Visual Basic Developer Center


http://msdn.microsoft.com/en-us/vcsharp/default - Visual C# Developer Center


http://windowsclient.net/default.aspx - The Official Microsoft WPF and Windows Forms Site


https://www.dreamspark.com/default.aspx - Microsoft DreamSpark - Microsoft DreamSpark is a platform for downloading free softwares for students.




JavaScript and jQuery


http://jquery.com/ - jQuery - The Write Less, Do More, JavaScript Library




Online Communities


http://dreamincode.net/ - DreamInCode.net - Programming and Web Development Help