[Solved] What’s wrong with this SQL?

by 


SELECT
      COALESCE(s.state, c.state) AS state
    , COALESCE(s.city, c.city) AS city
    , COALESCE(s.Suppliers, 0) AS Suppliers
    , COALESCE(c.Consumers, 0) AS Consumers
FROM (
      SELECT
            Tb_Supplier.State
          , Tb_Supplier.City
          , COUNT(Tb_Supplier.Name) AS Suppliers
      FROM Tb_Supplier
      GROUP BY
            Tb_Supplier.City
          , Tb_Supplier.State
) AS s
      FULL OUTER JOIN (
            SELECT
                  Tb_Consumer.State
                , Tb_Consumer.City
                , COUNT(Tb_Consumer.Name) AS Consumers
            FROM Tb_Consumer
            GROUP BY
                  Tb_Consumer.City
                , Tb_Consumer.State
      ) AS c ON s.state = c.state
                  AND s.city = c.city

The query above uses what I assume are “master tables” for Suppliers and Consumers, so rows in each should unique define a Supplier or Consumer. Hence count(distinct...) is not required in the above approach. A full outer join is used because there may be suppliers in state/city with no matching consumers and vice-versa. Due to this coalesce is used in the final select clause to deal with possible NULLs coming from either the supplier or consumer side.

Why you should stop using commas between tables:

-- Accidental Cross Join? (Cartesian Product)
-- or is it Deliberate
select * from table_one, table_two

In the above example each row of table_one is multiplied by all the rows of table_two. So, if both tables had 100 rows, the result is 100*100 = 10,000 rows. Did I want 10,000 rows?

You have no idea if I wanted it or not, it could be deliberate or just an accident.

select * from table_one CROSS JOIN table_two

But now I DO know that the cross join is deliberate.

Look back at your original question.

FROM 
    Tb_Supplier, Tb_Consumer
GROUP BY 
    Tb_Supplier.City, Tb_Supplier.State, Tb_Consumer.City, Tb_Consumer.State​

If you had 1,000 Suppliers and 100,000 consumers how many rows did you create in that from clause? (1,000 * 100,000 = 100,000,000) I’m quite certain that was accidental, and that is why you should stop using commas between tables in the from clause.

1

solved What’s wrong with this SQL?