左表有一个列名为Status,右表,只有一列Status。
现在用右表作为左标的Filter,也就是说只取左表中Status=右表Status的记录:SELECT *
FROM dbo.Mails AS L INNER JOIN @MailStatusFilters AS R ON (L.Status = R.Status)
当右表@MailStatusFilters为空表时,这时做内连接,返回的一定是一个空表。
我想实现相反的结果,即,当右表@MailStatusFilters为空表时(不指定Filter),返回整个左表,所有的Mail。
请问有什么办法可以实现?
谢谢。
现在用右表作为左标的Filter,也就是说只取左表中Status=右表Status的记录:SELECT *
FROM dbo.Mails AS L INNER JOIN @MailStatusFilters AS R ON (L.Status = R.Status)
当右表@MailStatusFilters为空表时,这时做内连接,返回的一定是一个空表。
我想实现相反的结果,即,当右表@MailStatusFilters为空表时(不指定Filter),返回整个左表,所有的Mail。
请问有什么办法可以实现?
谢谢。
1, 当右表不为空,那末实现 INNER JOIN的语义(返回仅左表.status = 右表.status)的记录;
2, 当右表为空,那末返回左表的全部记录。
SELECT *
FROM dbo.Mails AS L LEFT JOIN @MailStatusFilters AS R ON (L.Status = R.Status)
create table aa(rowid int,status int)
create table bb(rowid int,status int)insert into aa
select 1,1 union all
select 2,2 union all
select 3,5SELECT *
FROM aa AS L INNER JOIN bb AS R ON (L.Status = R.Status)
WHERE exists(select 1 from bb)
UNION ALL
SELECT *
FROM aa AS L LEFT JOIN bb AS R ON (L.Status = R.Status)
WHERE not exists(select 1 from bb)/*
rowid status rowid status
1 1 NULL NULL
2 2 NULL NULL
3 5 NULL NULL
*/
insert into bb
select 3,1 union all
select 4,2 union all
select 3,3 union all
select 4,4SELECT *
FROM aa AS L INNER JOIN bb AS R ON (L.Status = R.Status)
WHERE exists(select 1 from bb)
UNION ALL
SELECT *
FROM aa AS L LEFT JOIN bb AS R ON (L.Status = R.Status)
WHERE not exists(select 1 from bb)
/*
rowid status rowid status
1 1 3 1
2 2 4 2
*/
我理解,第一个select语句:SELECT *
FROM aa AS L INNER JOIN bb AS R ON (L.Status = R.Status)
WHERE exists(select 1 from bb)
的意思就是:将aa和bb作内连接(aa.status = bb.status),得到一个结果集,然后从结果集中选取bb.rowid不为空的那些列。对吗?
select m.* , n.* from tb1 , tb2 where m.status = n.status
else
select m.* from tb1 m
连接了不是浪费时间么?还得费劲写连接程序,直接
select * from 非空表
得了.
declare @str varchar(4000)
@from varchar(2000)
set @str='select * from 左表 '
if exists (select 1 from 右表)
begin
set @from=' a inner join 右表 b on a.Status=b.Status'
end
else
begin
set @from=''
end
set @str=@str+@from
exec (@str)
FROM aa AS L INNER JOIN bb AS R ON (L.Status = R.Status)
UNION ALL
SELECT L.*
FROM aa AS L
WHERE not exists(select status from bb group by status having count(*)>=1)