Brasciole Brasciole - 25 days ago 6
SQL Question

SQL Query Total Records

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

Answer

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?

Comments