Under what circumstances would these 3 queries return different number of results?

Posted on

Question :

Hello I have the following question:

Query 1:

SELECT b.id,
    (
    SELECT SUM(cb.invoice_amount) AS sum
    FROM payment_lines cb
    WHERE cb.batch_id = b.id
    ) AS batch_total_invoice_amount
FROM batches b;


Query 2:

SELECT b.id, SUM(cb.invoice_amount) 
FROM batches b 
 LEFT JOIN payment_lines cb ON b.id = cb.batch_id 
GROUP BY b.id
ORDR BY b.id desc;


Query 3:


SELECT batch_id, sum(invoice_amount) 
FROM payment_lines
GROUP BY batch_id
ORDER BY batch_id desc;

Answer :

The first query may return rows from batches where no payment-lines exist, ie. an outer join.

Investigate:

CREATE TABLE batches (id INT, name VARCHAR(200));
INSERT INTO batches VALUES
(1, 'name 1-1'),
(1, 'name 1-2'),
(2, 'name 2'),
(3, 'name 3'),
(4, 'name 4'),
(NULL, 'name NULL');

CREATE TABLE payment_lines (batch_id INT, invoice_amount INT);
INSERT INTO payment_lines VALUES
(1,100),
(1,200),
(2,111),
(2,222),
(3,NULL),
(5, 123),
(NULL,456);
SELECT b.id,
    (
    SELECT SUM(cb.invoice_amount) AS sum
    FROM payment_lines cb
    WHERE cb.batch_id = b.id
    ) AS batch_total_invoice_amount
FROM batches b
ORDER BY b.id desc;

SELECT b.id, SUM(cb.invoice_amount) 
FROM batches b 
 LEFT JOIN payment_lines cb ON b.id = cb.batch_id 
GROUP BY b.id
ORDER BY b.id desc;

SELECT batch_id, sum(invoice_amount) 
FROM payment_lines
GROUP BY batch_id
ORDER BY batch_id desc;
  id | batch_total_invoice_amount
---: | -------------------------:
   4 |                       null
   3 |                       null
   2 |                        333
   1 |                        300
   1 |                        300
null |                       null

  id | SUM(cb.invoice_amount)
---: | ---------------------:
   4 |                   null
   3 |                   null
   2 |                    333
   1 |                    600
null |                   null

batch_id | sum(invoice_amount)
-------: | ------------------:
       5 |                 123
       3 |                null
       2 |                 333
       1 |                 300
    null |                 456

db<>fiddle here

Leave a Reply

Your email address will not be published. Required fields are marked *