Table variable in SQL Server

Question

I am using SQL Server 2005. I have heard that we can use a table variable to use instead of LEFT OUTER JOIN.

What I understand is that, we have to put all the values from the left table to the table variable, first. Then we have to UPDATE the table variable with the right table values. Then select from the table variable.

Has anyone come across this kind of approach? Could you please suggest a real time example (with query)?

I have not written any query for this. My question is - if someone has used a similar approach, I would like to know the scenario and how it is handled. I understand that in some cases it may be slower than the LEFT OUTER JOIN.

Please assume that we are dealing with tables that have less than 5000 records.

Thanks

Answer 1

It can be done, but I have no idea why you would ever want to do it.

This realy does seem like it is being done backwards. But if you are trying this for your own learning only, here goes:

DECLARE @MainTable TABLE(
        ID INT,
        Val FLOAT
)

INSERT INTO @MainTable SELECT 1, 1
INSERT INTO @MainTable SELECT 2, 2
INSERT INTO @MainTable SELECT 3, 3
INSERT INTO @MainTable SELECT 4, 4

DECLARE @LeftTable TABLE(
        ID INT,
        MainID INT,
        Val FLOAT
)

INSERT INTO @LeftTable SELECT 1, 1, 11
INSERT INTO @LeftTable SELECT 3, 3, 33

SELECT  *,
        mt.Val + ISNULL(lt.Val, 0)
FROM    @MainTable mt LEFT JOIN
        @LeftTable lt ON mt.ID = lt.MainID

DECLARE @Table TABLE(
        ID INT,
        Val FLOAT
)

INSERT INTO @Table
SELECT  ID,
        Val
FROM    @MainTable

UPDATE  @Table
SET     Val = t.Val + lt.Val
FROM    @Table t INNER JOIN
        @LeftTable lt ON t.ID = lt.ID

SELECT *
FROM    @Table