Using EXISTS as a column in TSQL

Question

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

score 2 · Accepted Answer · edited Apr 13 '17 at 12:42

2

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

edited Apr 13 '17 at 12:42

Community

1
1

answered Sep 13 '13 at 13:44

Martin Smith

438,706
87
741
845

Ack, yes, you're correct - sorry was typing off the top of my head. Have added the missing `COUNT(*)` – freefaller Sep 13 '13 at 13:52
I wasn't aware of `COUNT` being faster than `EXISTS` in certain situations... will definitely bare that in mind. Thank you. – freefaller Sep 13 '13 at 13:53
`TOP 1 1` should be even faster ;) – DrCopyPaste Sep 13 '13 at 13:59
@DrCopyPaste - No it isn't. SQL Server is well aware that `EXISTS` only needs a single row. And there is no benefit of using the literal `1` compared to `*` either. – Martin Smith Sep 13 '13 at 14:00
But how can `COUNT` be faster than `EXISTS` then, I mean for count you would have to pull every row, I dont understand – DrCopyPaste Sep 13 '13 at 14:02
@DrCopyPaste - Did you read the answer I linked? In the particular case there using `EXISTS` in a `CASE` prevented a particular transformation meaning that unnecessary additional rows were read from the second table. The predicate `WHERE T1.ID BETWEEN 5000 AND 7000` could also have been applied to `T2` to limit the number of `T2` rows read but wasn't in the `EXISTS` case. – Martin Smith Sep 13 '13 at 14:05
Sorry for my ignorance ;) Thanks for replying – DrCopyPaste Sep 13 '13 at 14:12

DrCopyPaste · Answer 2 · 2013-09-13T13:58:30.873

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

Thanks for your input, hadn't thought of the `TOP 1` way of doing it — freefaller, Sep 13 '13 at 13:55

score 1 · Answer 3 · answered Sep 13 '13 at 13:48

1

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

answered Sep 13 '13 at 13:48

wcm

9,045
7
39
64

I did wonder about CTE's, but my gut feeling is that it wouldn't be particularly good performance (but that is purely gut feeling without any proof). Thanks for your input – freefaller Sep 13 '13 at 13:54

Using EXISTS as a column in TSQL

3 Answers3

Linked