Sql Server: Table Variable Used in a Inner Join

SQL Server: joining a declared table variable with local server tables

Just give a Alias name to table variable and use it for reference

SELECT *
FROM   tbl1
       RIGHT JOIN tbl2
               ON tbl1.foofield = tbl2.foofield
       RIGHT JOIN @fooTable FT -- Here 
               ON tbl2.foofield = FT.foofield; 
                                --^^-- Here 

Join on Table Variable if it has data

Of course, a relatively simple approach (assuming your SELECT statement is not too complicated and you don't mind "duplicating" it) would be:

IF EXISTS (SELECT 1 FROM @MyTableVariable)
BEGIN
    SELECT ...
    FROM MyTable m
    INNER JOIN @MyTableVariable v ON v.Id = m.Id, --But only if @MyTableVariable  has data
    Where UserId = 1;
END
ELSE
BEGIN
    SELECT ...
    FROM MyTable m
    Where UserId = 1;
END

Otherwise

SELECT ...
FROM MyTable m
LEFT OUTER JOIN @MyTableVariable v ON v.Id = m.Id, --But only if @MyTableVariable  has data
Where UserId = 1
AND
(
    (EXISTS (SELECT 1 FROM @MyTableVariable)
         AND v.Id Is Not NULL)
    OR
    (NOT EXISTS (SELECT 1 FROM @MyTableVariable))
);

I'll stand corrected if I'm wrong (just don't have the opportunity to test it myself right now), but I believe that even if NULL was passed in as the @MyTableVariable value, SQL would still see it as an empty table (with the corresponding structure of the UDT)

Join 2 Table Variables in SQL

Since the IDs are not in sequential order and can be random, I would recommend using an Identity on the table variables and joining on that:

DECLARE @InventoryIDList TABLE(JoiningID INT IDENTITY(1,1), ID INT)
DECLARE @ProductSupplierIDList TABLE(JoiningID INT IDENTITY(1,1), ID INT)

INSERT INTO @InventoryIDList 
VALUES 
(123),
(456),
(789),
(111)

INSERT INTO @productsupplierIDList 
VALUES 
(999),
(888),
(777),
(666)

SELECT i.id, p.id
FROM @inventoryIDList i
INNER JOIN @productsupplierIDList p
    oN i.joiningid = p.JoiningID

sql INNER JOIN table variable ON VS. INNER JOIN (select) ON

SQL Server does not maintain detailed statistics for table variables or automatically recompile to reflect less granular cardinality information changes (without TF 2453) so will generally assume that they output a single row.

This means that sometimes you will get a sub optimal join strategy. The second version can use statistics and also avoids the overhead of inserting the intermediate results into a temporary object.

However if the second query is expensive to evaluate as SomeDate is not indexed you may get improved performance from materialising this up front (compared to repeatedly re-evaluating).

You could also consider using a #temp table as this avoids the statistics issue. Some people suggest never using a table variable in JOINs

Use variable as INNER JOIN in dynamic query

Table variables are not inherited in the exec. However, temporary tables are. So I would recommend:

CREATE TABLE #SearchKeys (
    DesignKey INT
);

 . . . 

DECLARE @Sql NVARCHAR(MAX) = '
DECLARE @DList TABLE (
    [ProjectKey] INT,
    [Project Name] NVARCHAR(255)
    ...
   );
INSERT INTO @DList ( . . . )
    SELECT [p].[ProjectKey], [p].[Name] AS [Project Name], ....
    FROM [Project] [p] JOIN
         #SearchKeys [SK]
         ON [D].[DesignKey] = [SK].[DesignKey]
'

Sql Server Performance: table variable inner join vs multiple conditions in where clause

In most cases the first approach will win as table variable does not use statistics. You'll notice big performance decrease with big amount of data. When you have just few values then there is not supposed to be any noticeable difference.

---

Related Topics