T-SQL join table only when the table is not empty

Question

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

TableOne 101 102 103 104 105 106 TableTwo 101 102 103 104 

and you want to make a connection between them only when TableTwo is not empty. This can be done using the IF example, but in my real situation this will lead to a lot of code duplication.

I will 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 does not work for me - it only works when the second table is not empty, if it is empty, nothing is returned.

Does anyone know if this is possible?

Answer 1

I assume you want to select everything if there is no row in TableTwo . You need 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