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