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
You may use
SELECT COUNT(A.UserID) FROM Users A LEFT JOIN Orders B ON A.UserID = B.UserID WHERE B.UserID IS NULL
SELECT COUNT(A.USERID) FROM Users A WHERE NOT EXISTS (SELECT UserID FROM Orders X WHERE X.UserID = A.UserID)
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?