如何使用带有IF EXISTS子查询的SQL Select语句？

Question

如何使用IF EXISTS语句从子查询中选择布尔值（SQL Server）？ 代码应该类似于：

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

- Nalaka526

Answer 1

使用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

---

如果 TABLE2.ID 是唯一的或者是主键，你也可以这样使用：

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

- ypercubeᵀᴹ

Answer 2

你还可以使用ISNULL和select语句来获得此结果

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

- Purplegoldfish

---

我刚注意到你说你想要布尔值，选择1/0可能比True / False更好。 - Purplegoldfish

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;

- onedaywhen

---

将 INTERSECT、UNION 和 EXCEPT 结合在一个查询中，给你点赞！ - ypercubeᵀᴹ

同样的事情减1——用不必要的复杂性炫耀，这会使代码更难读懂和维护。 - Anon_unique

@Anon_unique 哈哈，对我来说这段代码非常简单，可能是因为我习惯于集合操作。 - onedaywhen

Answer 4

使用CASE语句，可以这样做：

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

- satnhak

Answer 5

您可以使用 EXISTS 来检查一个列值是否存在于另一张表中。

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

例子：

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

输出：

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

- Stevoisiak