Is this SQL query with an EXISTS the most performant way of returning my result?

Question

I'm trying to find the following statistic: How many users have made at least one order

(yeah, sounds like homework .. but this is a simplistic example of my real query).

Here's the made up query

SELECT COUNT(UserId)
FROM USERS a
WHERE EXISTS(
    SELECT OrderId
    FROM ORDERS b
    WHERE a.UserId = b.UserId
)

I feel like I'm getting the correct answers but I feel like this is an overkill and is inefficient.

Is there a more efficient way I can get this result?

If this was linq I feel like I want to use the Any() keyword....

This could help: http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance — sgeddes, Sep 16 '14 at 00:13
[_Your eyes can deceive you. Don't trust them._](http://en.wikiquote.org/wiki/Star_Wars_Episode_IV:_A_New_Hope) Your query is fine. Trust the compiler to find the optimal plan based on your data and inidices. — D Stanley, Sep 16 '14 at 00:19

score 3 · Accepted Answer · edited May 23 '17 at 12:12

3

It sounds like you just could use COUNT DISTINCT:

SELECT COUNT(DISTINCT UserId)
FROM ORDERS

This will return the number of distinct values of UserId appear in the table OrderId.

In response to sgeddes's comment, to ensure that UserId also appears in Users, simply do a JOIN:

SELECT COUNT(DISTINCT b.UserId)
FROM ORDERS b
  JOIN USERS a 
    ON a.UserId = b.UserId

edited May 23 '17 at 12:12

Community

answered Sep 16 '14 at 00:14

p.s.w.g

This could work, but it assumes the `UserId` from the `Orders` table exists in the `Users` table as well. – sgeddes Sep 16 '14 at 00:16
@sgeddes I included a case for handling that if necessary. – p.s.w.g Sep 16 '14 at 00:20

score 1 · Answer 2 · answered Sep 16 '14 at 00:19

1

Select count(distinct u.userid)
From USERS u
Inner join ORDERS o
On o.userid = u.userid

answered Sep 16 '14 at 00:19

radar

score 1 · Answer 3 · answered Sep 16 '14 at 00:26

Your query should be fine, but there are a few other ways to calculate the count:

SELECT COUNT(*)
FROM USERS a
WHERE UserId IN (
    SELECT UserId 
    FROM ORDERS b
)

or

SELECT COUNT(DISTINCT UserID)
FROM USERS a
INNER JOIN ORDERS b ON a.UserID = b.UserID

The only way to know which is faster is to try each method and measure the performance.

3 Answers3