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)