仅当表不为空时，T-SQL联接表 [英] T-SQL join table only when the table is not empty

问题描述

我有一个带有一列(RecordID)的下表:

TableOne
101
102
103
104
105
106

TableTwo
101
102
103
104

，并且仅当TableTwo不为空时才希望在它们之间进行联接.可以使用示例IF语句完成此操作，但在我的实际情况下，这将导致大量代码重复.

我尝试了以下方法:

SELECT * FROM
TableOne T1
WHERE exists (select 1 from TableTwo where T1.RecordID=RecordID)
      and exists (select 1 from TableTwo)

使用此答案，但是相同的逻辑是对我不起作用-它仅在第二个表不为空时起作用，如果它为空，则不返回任何内容.

有人知道这是否可能吗?

解决方案

如果TableTwo中没有行，我假设您要选择全部.您需要OR和NOT EXISTS:

SELECT 
   T1.* 
FROM
   TableOne T1
WHERE 
   EXISTS(SELECT 1 from TableTwo WHERE T1.RecordID=RecordID)
   OR NOT EXISTS(SELECT 1 FROM TableTwo)

SQL小提琴

I have the following tables with one column (RecordID):

TableOne
101
102
103
104
105
106

TableTwo
101
102
103
104

and want to make join between them only when TableTwo is not empty. This could be done with sample IF statement, but in my real situation this would lead to a lot of code duplication.

I have try the following:

SELECT * FROM
TableOne T1
WHERE exists (select 1 from TableTwo where T1.RecordID=RecordID)
      and exists (select 1 from TableTwo)

using this answer , but the same logic is not working for me - it works only when the second table is not empty, if it is empty, nothing is return.

Has anyone know if this is possible?

解决方案

I assume you want to select all if there's no row in TableTwo. You need an OR and NOT EXISTS:

SELECT 
   T1.* 
FROM
   TableOne T1
WHERE 
   EXISTS(SELECT 1 from TableTwo WHERE T1.RecordID=RecordID)
   OR NOT EXISTS(SELECT 1 FROM TableTwo)

SQL-Fiddle

这篇关于仅当表不为空时，T-SQL联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！