Adding an unused table after FROM changes retrieved data

user_15523568 0
Viewed 32

I am referring to the Chinook database, which i am using to learn SQLite. This query retrieves the number of invoices for each CustomerId, as I wanted:

    select i.customerid, count(i.invoiceid)
    from invoices as i
    group by i.customerid

returns:

+------------+--------------------+
| CustomerId | count(i.invoiceid) |
+------------+--------------------+
|          1 |                  7 |
|          2 |                  7 |
|          3 |                  7 |
...

But as i was building a more complex query i observed something that i cannot explain:

    select i.customerid, count(i.invoiceid)
    from invoices as i, customers as c
    group by i.customerid

returns:

+------------+--------------------+
| CustomerId | count(i.invoiceid) |
+------------+--------------------+
|          1 |                413 |
|          2 |                413 |
|          3 |                413 |
...

Turns out 413 = 7 * 59, and 59 is the number of distinct CustomerID's. There must be some fundamental SQL behavior that i am misunderstanding here, because I would expect no difference by adding "customers as c" in the "from" clause, since I am not using it yet. Can anyone enlighten me here on what is happening?

sql
2 Answers

Never use commas in the FROM clause. Only use proper, explicit, standard, readable JOIN syntax.

Your query is producing a Cartesian product of the rows in the two tables. Then your aggregation counts the number of rows, for each customer, in the Cartesian product.

You need something like this:

select i.customerid, count(i.invoiceid)
from invoices i join
     customers c
     on i.customerid = c.customerid
group by i.customerid
user_11440350

You are performing a cross join which is the cartesian product of the rows of your 2 tables. You were right about the origin of the 413 value.
With a cross join, if table A has 5 rows and table B has 7 rows, it will produce a result of 5 * 7 = 35 rows.

When joining tables, you then need to add a join condition which will filter unrelated rows (cross joins are rarely what you want):

SELECT i.customerid, count(i.invoiceid)
FROM invoices as i, customers as c
WHERE i.customerid = c.id    -- join condition
GROUP BY i.customerid

But the recommended syntax for join is explicit (no comma):

SELECT i.customerid, count(i.invoiceid)
FROM invoices as i
    JOIN customers as c       -- explicit join
    ON i.customerid = c.id    -- join condition
GROUP BY i.customerid

But this will perform an INNER JOIN by default which requires that rows from invoices table matches at least 1 row from customers, and vice-versa. If you still want to display customers with 0 invoices, you need to use LEFT JOIN to keep rows from the left table (first one of the FROM clause) even if they have no match with the right table:

SELECT i.customerid, count(i.invoiceid)
FROM invoices as i
    LEFT JOIN customers as c  -- keep customers without invoices
    ON i.customerid = c.id    -- join condition, unchanged
GROUP BY i.customerid
user_63497510
Related