這 3 個查詢在什麼情況下會返回不同數量的結果？

您好我有以下問題：

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;

第一個查詢可能會從不存在支付行的批次中返回行，即。外連接。

調查：

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;

 編號 | batch_total_invoice_amount
---: | -------------------------:
 4 | *空*
 3 | *零*
 2 | 333
 1 | 300
 1 | 300
*空*| *零*

 編號 | 總和（cb.invoice_amount）
---: | ---------------------:
 4 | *空*
 3 | *零*
 2 | 333
 1 | 600
*空*| *零*

批號 | 總和（發票金額）
-------: | ------------------:
 5 | 123
 3 | *空值*
 2 | 333
 1 | 300
    *空*| 456

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/286609