T-SQL: How to join @variable tables [duplicate]

问题:

Possible Duplicate:
T-SQL: How to join @variable tables (another try)

First: I'm using SQL Server 2008. In a complex algorithm that involves a lot of data, I have been using a technical of creating intermediate table variables:

DECLARE @table AS TABLE (Col1 INT, Col2 VARCHAR(100))

Unfortunately, SQL Server does not support JOINning @variable tables, it is only allowed to join "true" tables, those in the database.

I could do the "manual" join, like

FROM @table1 t1, @table2 t2
WHERE t1.Id = t2.Id

This results in a INNER JOIN, but this is wrong for me. The question is: How do FULL JOIN two @variable tables?

回答1:

What do you mean by SQL doesn't support Joining table variables?

It works for me

DECLARE @table1 AS TABLE (Col1 INT, Col2 VARCHAR(100))
DECLARE @table2 AS TABLE (Col1 INT, Col2 VARCHAR(100))

SELECT *
FROM @table1 t1
FULL JOIN @table2 t2 on t1.Col1 = t2.Col1

回答2:

You should be able to do a join using an @tableVariable

SELECT * 
FROM table1 t
FULL JOIN @tableVariable tv
ON (tv.col = cnc.col)

Could it have anything to do with your compatability setting? (mine is at 100)

sp_dbcmptlevel 'database_name'

ALTER DATABASE database_name 
    SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

回答3:

I'm not sure what you're asking, as joining works just fine for table variables. See this example:

declare @table as table (Col1 int, Col2 varchar(100))
declare @table2 as table (Col1 int, Col2 varchar(100))

insert into @table
select 1, 'A'
union all
select 1, 'C'
union all
select 1, 'D'

insert into @table2
select 2, 'A'
union all
select 2, 'B'
union all
select 2, 'D'
union all
select 2, 'E'

select
    *
from
    @table t1 full outer join
    @table2 t2 on t1.Col2 = t2.Col2

select
    *
from
    @table t1 left join
    @table2 t2 on t1.Col2 = t2.Col2

select
    *
from
    @table t1 right join
    @table2 t2 on t1.Col2 = t2.Col2

select
    *
from
    @table t1 join
    @table2 t2 on t1.Col2 = t2.Col2