Using EXISTS as a column in TSQL

问题:

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

(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

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

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

回答1:

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.

The article Subqueries in CASE Expressions discusses this further.

In some cases a COUNT query can actually perform better though as discussed here

回答2:

I like the other guys sql better but i just wrote this:

with bla as (
    select t2.username, isPresent=CAST(1 AS BIT)
    from t2
    group by t2.username
)
select t1.*, isPresent = isnull(bla.isPresent, CAST(0 AS BIT))
from t1 
    left join blah on t1.username=blah.username

回答3:

From what you wrote here I would alter your first query into something like this

SELECT
        T1.[UserName], ISNULL(
            (
                SELECT
                        TOP 1 1
                    FROM [AnotherTable]
                    WHERE EXISTS
                        (
                            SELECT
                                    1
                                FROM [AnotherTable] AS T2
                                WHERE T1.[UserName] = T2.[UserName]
                        )
            ), 0)
    FROM [UserTable] T1

But actually if you use TOP 1 1 you would not need EXISTS, you could also write

SELECT
        T1.[UserName], ISNULL(
            (
                SELECT
                        TOP 1 1
                    FROM [AnotherTable] AS T2
                    WHERE T1.[UserName] = T2.[UserName]
            ), 0)
    FROM [UserTable] T1