求两表联全的sql,该怎么解决

--> 测试数据: @表1declare @表1 table (id int,c1 varchar(1),c2 int)insert into @表1select 1,'a',0 union allselect 2,'b',0 union allselect 3,'c',0 union allselect 4,'d',0 union allselect 5,'e',0--> 测试数据: @表3declare @表3 table (id int,c1 varchar(1),c3 varchar(3))insert into @表3select 1,'a','AAA' union allselect 2,'b','BBB' union allselect 3,'c','AAA' union allselect 4,'d','AAA'declare @sql varchar(20) set @sql='AAA' --可以把参数改成BBBselect     a.id,a.c1,    case when [email protected] then null else b.c3 end as c2,    case when b.c3 is null or b.c3<>@sql then 1 else 0 end as c3from @表1 a left join @表3 b on a.id=b.id/*id          c1   c2   c3----------- ---- ---- -----------1           a    NULL 02           b    BBB  13           c    NULL 04           d    NULL 05           e    NULL 1*/
------解决方案--------------------
SQL code
--> 测试数据: @表1declare @表1 table (id int,c1 varchar(1),c2 int)insert into @表1select 1,'a',0 union allselect 2,'b',0 union allselect 3,'c',0 union allselect 4,'d',0 union allselect 5,'e',0--> 测试数据: @表3declare @表3 table (id int,c1 varchar(1),c3 varchar(3))insert into @表3select 1,'a','AAA' union allselect 2,'b','BBB' union allselect 3,'c','AAA' union allselect 4,'d','AAA'declare @sql varchar(20) set @sql='AAA' --可以把参数改成BBBselect     a.id,a.c1,    case when [email protected] then b.c3 else null end as c2,    case when [email protected] then 1 else 0 end as c3from @表1 a left join @表3 b on a.id=b.id/*id          c1   c2   c3----------- ---- ---- -----------1           a    AAA  12           b    NULL 03           c    AAA  14           d    AAA  15           e    NULL 0*/
------解决方案--------------------
SQL code
if object_id=('tab1') is not nulldrop table tab1gocreate table tab1(id int,c1 varchar(1),c2 int)insert into tab1select 1,'a',0 union allselect 2,'b',0 union allselect 3,'c',0 union allselect 4,'d',0 union allselect 5,'e',0if object_id=('tab3') is not nulldrop table tab3gocreate table tab3(id int,c1 varchar(1),c2 int)insert into tab3select 1,'a','AAA' union allselect 2,'b','BBB' union allselect 3,'c','AAA' union allselect 4,'d','AAA'--开始查询SELECT a.id,       a.c1,       CASE          WHEN b.c3 ='AAA' --此处可更换'BBB'         THEN b.c3         ELSE NULL       END AS c2,       CASE          WHEN b.c3 ='AAA' --此处可更换'BBB'         THEN 1         ELSE 0       END AS c3FROM   tab1 a,tab3 bWHERE a.id = b.id/*id          c1   c2   c3----------- ---- ---- -----------1           a    AAA  12           b    NULL 03           c    AAA  14           d    AAA  15           e    NULL 0*/
------解决方案--------------------
探讨

这个表3里不止AAA和BBB，还有别的数值

这里道个歉上面那个结果写反了
应该是这样的
我想联合表1和表3查询BBB的结果如下：
1 a Null 0
2 b BBB 1
3 c Null 0
4 d Null 0
5 e　Null 1

或者查询AAA的结果如下：
1 a AAA 1
2 b Null 0
3 c AAA 1
4 d AAA 1
5 e　Null……

------解决方案--------------------
select a.id,a.col,b.cols,(cast when isnull(cols,'')='' then 0  else then 1 end) from 表1 a left join 表3 on a.id=b.id