使用EXISTS作为TSQL中的一列 [英] Using EXISTS as a column in TSQL

问题描述

是否可以将EXISTS的值用作查询的一部分?

Is it possible to use the value of EXISTS as part of a query?

(请注意:不幸的是，由于客户端的限制，我需要与SQLServer 2005兼容的答案！)

(Please note: unfortunately due to client constraints, I need SQLServer 2005 compatible answers!)

因此，当返回一组结果时，其中一列是一个布尔值，该值指示子查询是否将返回任何行.

So when returning a set of results, one of the columns is a boolean value which states whether the subquery would return any rows.

例如，我想返回一个用户名列表，以及是否有一个不同的表包含每个用户的任何行.以下内容在语法上不正确，但希望能使您了解我的意思...

For example, I want to return a list of usernames and whether a different table contains any rows for each user. The following is not syntactically correct, but hopefully gives you an idea of what I mean...

SELECT T1.[UserName],
    (EXISTS (SELECT * 
             FROM [AnotherTable] T2 
             WHERE T1.[UserName] = T2.[UserName])
    ) AS [RowsExist]
FROM [UserTable] T1

其中的结果集包含一个名为[UserName]的列和一个名为[RowsExist]的布尔列.

Where the resultant set contains a column called [UserName] and boolean column called [RowsExist].

明显的解决方案是使用CASE，如下所示，但我想知道是否有更好的方法...

The obvious solution is to use a CASE, such as below, but I wondered if there was a better way of doing it...

SELECT T1.[UserName],
    (CASE (SELECT COUNT(*)
           FROM [AnotherTable] T2 
           WHERE T1.[UserName] = T2.[UserName]
          )
          WHEN 0 THEN CAST(0 AS BIT)
          ELSE CAST(1 AS BIT) END
    ) AS [RowsExist]
FROM [UserTable] T1

推荐答案

您的第二个查询不是有效的语法.

Your second query isn't valid syntax.

SELECT T1.[UserName],
       CASE
         WHEN EXISTS (SELECT *
                      FROM   [AnotherTable] T2
                      WHERE  T1.[UserName] = T2.[UserName]) THEN CAST(1 AS BIT)
         ELSE CAST(0 AS BIT)
       END AS [RowsExist]
FROM   [UserTable] T1 

通常还可以，可以将其实现为半联接.

Is generally fine and will be implemented as a semi join.

文章 CASE表达式中的子查询对此进行进一步讨论.

The article Subqueries in CASE Expressions discusses this further.

在某些情况下，尽管 查看全文