CODEWITHSUNDEEP
sqlt-sql
Pure.Krome
Pure.Krome

Reputation: 86937

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

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....

Upvotes: 1

Views: 45

Answers (3)

D Stanley
D Stanley

Reputation: 152491

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.

Upvotes: 1

p.s.w.g
p.s.w.g

Reputation: 148980

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

Upvotes: 3

radar
radar

Reputation: 13425

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

Upvotes: 1

Related Questions