Mysql – Inner join or cross join Confusion

The second query. But why are you comparing them?

They may return the same data but they are answering two different questions. The first retrieves all records from doctors where a record exists in clinic with id=1. The second retrieves all records from doctors with clinic_id=1 regardless of whether a record exists in clinic.

In the absence of a foreign key constraint between the two tables, the queries are not comparable.

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors.

Something like this will work:

SELECT 
    m.month, 
    p.name, 
    COALESCE(r.receipts_quantity, 0) AS receipts_quantity, 
    COALESCE(s.sales_quantity, 0)    AS sales_quantity
FROM 
    product AS p
  CROSS JOIN
    ( VALUES (1),(2),(3),(4),(5),(6) ) AS m(month)     -- months wanted in the report
  LEFT OUTER JOIN 
    ( SELECT month, product_id, SUM(quantity) AS receipts_quantity
      FROM receipts
      GROUP BY month, product_id
    ) AS r
    ON  p.id_product = r.product_id
    AND m.month = r.month
  LEFT OUTER JOIN  
    ( SELECT month, product_id, SUM(quantity) AS sales_quantity
      FROM sales
      GROUP BY month, product_id
    ) AS s
    ON  p.id_product = s.product_id
    AND m.month = s.month ;

If you want to avoid having results for products that did not have any activity in a month, you can add the condition:

WHERE r.month IS NOT NULL 
   OR s.month IS NOT NULL