How to use SQL Select statement with IF EXISTS sub query?

Question

How to select Boolean value from sub query with IF EXISTS statement (SQL Server)?

It should be something like :

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

Ossuary

Answer 1

Use CASE:

SELECT 
  TABLE1.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 Unique or a Primary Key, you could also use this:

SELECT 
  TABLE1.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

Cholent

Answer 2

You can also use ISNULL and a 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

Hypnos

I just noticed you said you wanted bool value, it may be better to select 1/0 rather than True / False – Hypnos

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;

Knowlton

+1 for combining INTERSECT, UNION and EXCEPT in one query! – Marcoux

Minus 1 for the same thing -- showing off with unnecessary complexity that would be much harder to read and maintain. – Anderaanderea

@Anderaanderea lol for me this code is extremely simple, maybe because I think set based. – Knowlton

Answer 4

Use a CASE statement and do it like this:

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

Bondage

Answer 5

You can use EXISTS to check if a column value exists in a different table.

SELECT
    TABLE1.id,
    EXISTS (SELECT 1 FROM TABLE2 WHERE TABLE2.id = TABLE1.id) AS columnName
FROM TABLE1

Example:

CREATE TABLE TABLE1 (
    id INTEGER PRIMARY KEY,
    some_column TEXT NOT NULL
);
CREATE TABLE TABLE2 (
    id INTEGER PRIMARY KEY,
    some_column TEXT NOT NULL
);

INSERT INTO TABLE1 VALUES
    (111, 'lorem ipsum'),
    (222, 'and'),
    (333, 'some'),
    (444, 'random'),
    (123, 'strings');
 
INSERT INTO TABLE2 VALUES
    (111, 'lorem ipsum'),
    (444, 'random'),
    (123, 'strings');

SELECT
    TABLE1.id,
    EXISTS (SELECT 1 FROM TABLE2 WHERE TABLE2.id = TABLE1.id) AS columnName
FROM TABLE1

Output:

id	someColumn
111	1
123	1
222	0
333	0
444	1

Azine