How to use SQL Select statement with IF EXISTS subquery?

Question

How to select a boolean value from a subquery using the IF EXISTS statement (SQL Server)?

It should be something like:

SELECT TABEL1.Id, NewFiled = (IF EXISTS(SELECT Id FROM TABLE2 WHERE TABLE2.ID = TABEL1.ID) SELECT 'TRUE' ELSE SELECT 'FALSE') FROM TABEL1 

Answer 1

Use CASE :

 SELECT TABEL1.Id, CASE WHEN EXISTS (SELECT Id FROM TABLE2 WHERE TABLE2.ID = TABLE1.ID) THEN 'TRUE' ELSE 'FALSE' END AS NewFiled FROM TABLE1 

---

If TABLE2.ID is a unique or primary key, you can also use this:

 SELECT TABEL1.Id, CASE WHEN TABLE2.ID IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END AS NewFiled FROM TABLE1 LEFT JOIN Table2 ON TABLE2.ID = TABLE1.ID 

Answer 2

You can also use ISNULL and the select statement to get this result.

 SELECT Table1.ID, ISNULL((SELECT 'TRUE' FROM TABLE2 WHERE TABLE2.ID = TABEL1.ID),'FALSE') AS columName, etc FROM TABLE1 

Answer 3

 SELECT Id, 'TRUE' AS NewFiled FROM TABEL1 INTERSECT SELECT Id, 'TRUE' AS NewFiled FROM TABEL2 UNION SELECT Id, 'FALSE' AS NewFiled FROM TABEL1 EXCEPT SELECT Id, 'FALSE' AS NewFiled FROM TABEL2; 

Answer 4

Use the CASE statement and execute it as follows:

 SELECT T1.Id [Id] ,CASE WHEN T2.Id IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END [Has Foreign Key in T2] FROM TABLE1 [T1] LEFT OUTER JOIN TABLE2 [T2] ON T2.Id = T1.Id