0

I'm really struggling with this query. I have 2 tables, one for "users" and one for "orders". "users" has a field "user_id" and "orders" also has a field "user_id".

I need to know total records of Users with 0 records in the "orders" table. In other words, if I have 1000 users registered in the "users" table, how many of those never placed an order on the site.

I tried to come with something like this but of course it's incorrect:

SELECT Count(u.user_id) as tRecs, o.orderid
FROM users u
    INNER JOIN orders o ON u.user_id = o.user_id
jarlh
  • 42,561
  • 8
  • 45
  • 63
Brasciole
  • 377
  • 3
  • 8
  • 19

1 Answers1

3

You may use Left Join

SELECT COUNT(A.UserID) FROM Users A LEFT JOIN Orders B ON A.UserID = B.UserID WHERE B.UserID IS NULL

or Not Exists

SELECT COUNT(A.USERID) FROM Users A WHERE NOT EXISTS (SELECT UserID FROM Orders X WHERE X.UserID = A.UserID)

or Not In

SELECT COUNT(A.USERID) FROM Users A WHERE A.UserID NOT IN (SELECT UserID FROM Orders)

Depending on your DBMS for performance you wanna see this What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

Community
  • 1
  • 1
Esty
  • 1,882
  • 3
  • 17
  • 36
  • 2nd one you have the alias wrong (should be where X.UserID = A.UserID). And won't let last one have issues on many DBMS when number of records in Orders goes above a certain number? – Tibrogargan Nov 07 '16 at 03:13
  • @Tibrogargan, did you mean `Not IN` issues? – Esty Nov 07 '16 at 03:19
  • 1
    Yeah. A few months ago a project I was working on got tripped up by trying to put more than *X* values in a `NOT IN` clause. May have not have stemmed from the DBMS though – Tibrogargan Nov 07 '16 at 03:20
  • How on earth we people face same types of problem together !!! I also faced that later then I always rely on `join` or `exists`. – Esty Nov 07 '16 at 03:26