如何使用SUM并返回所有行，即使没有任何总和？

Question

I have a table USERS and a table ORDERS. In my backend office I'm attempting to output a table with all users (customers) and SUM their individual order total, so to simplify I'm saying:

SELECT users.id, SUM(orders.total) as spent FROM users
    JOIN orders ON users.id=orders.customer_id GROUP BY users.id

(Note: do not pay attention to the syntax, this is just to illustrate the point. the syntax is fine when I run it.)

I now have say 4 users in total and the ORDERS table looks something like this:

order_id  customer_id  total
1         1            25
2         2            10
3         1            5   

Then my query will output ONLY those users that can be found in the ORDERS table and my backend customer overview table will look unfortunately like this:

Customer ID      Spent in Total
1                30
2                10

ignoring completely the other 2 users who have not yet placed any orders. What I want to see is this:

Customer ID      Spent in Total
1                30
2                10
3                0
4                0

Is there a way to do this?

My guess is that it has something to do with special joins like inner, outer, but I don't know the difference there.

Also what I thought about was to run two queries, selecting * from users and then running a foreach to sum up order total, but this seems inefficient.

Answer 1

You need a left join, and on some (old) versions of MySQL also an IFNULL().

SELECT 
  users.id, 
  IFNULL(SUM(orders.total),0) as spent 
FROM users
LEFT JOIN orders ON users.id=orders.customer_id 
GROUP BY users.id