0

Can you join table variables with tables

KofiDec 28, 2019 | edited Dec 28, 2019 - by @sqldibia

Can you join table variables with tables? Almost all the examples i've found online execute with error

Emeka Okeke Author Dec 28, 2019
0

Yes of course you can join normal T-SQL tables with table variables.

Table variables are just like any other tables except that it belongs to the current connection. So just like local temp tables, it is discarded once the connection is closed.

Here is how you can join table variables with normal table:



CREATE TABLE dbo.TEST(ID INT,NAME VARCHAR(100))
INSERT dbo.TEST(ID,NAME)
VALUES
(1,'Mekus'),
(2,'Jane')

DECLARE @TBL TABLE(ID INT,NAME VARCHAR(100))
INSERT @TBL(ID,NAME)
VALUES
(1,'Mekus'),
(2,'Jane')

--The join
SELECT A.ID,A.NAME FROM @TBL A
INNER JOIN dbo.TEST B ON A.ID=B.ID

You can also join table variables with TSQL derived tables. Example:



SELECT A.ID,A.NAME FROM @TBL A
INNER JOIN (SELECT ID,NAME FROM dbo.TEST) B ON A.ID=B.ID

In addition you can also join them with common table expressions. Example:



;WITH CTE AS(SELECT ID,NAME FROM dbo.TEST)

SELECT A.ID,A.NAME FROM @TBL A
INNER JOIN CTE B ON A.ID=B.ID

Although you cannot directly create indexes on table variables, you can do so indirectly because it allows primary keys. And primary keys by default are unique clustered indexes.

Table variables even allow identity values as well. Example:



DECLARE @TBL TABLE(ID INT IDENTITY(1,1) PRIMARY KEY,NAME VARCHAR(100))
INSERT @TBL(ID,NAME)
VALUES
('Mekus'),
('Jane')

So as you can see, table variables are just like regular tables including derived tables and CTEs. The only difference it has with normal tables is that its scope is limited to the current connection. This means once the connection closes, it is dropped.

0