当我学习左外联接时,我得出了的结论
左外部联接B=A中的所有内容和B中的公共内容镜像结果表中的相应值,A中与B表没有公共值的其他值在B侧具有空值。
因此,如果A有15个值,B有29个值(5个公共值(,那么下面的查询结果将是15。或者,如果A有15个值,B有10个值(5个公共值(,结果仍然是15。
select count(*) from
A left outer join B
on A.name=B.name;
我的问题:
我有一个数字数据库。客户表,付款表。它们分别有59914596行。
当我运行查询时:(我期望14596,得到14596(
select count(*) from
payment left outer join customer
on payment.customer_id=customer.customer_id;
但当我换桌子时;(我预计599,但得到14596(
select count(*) from
customer left outer join payment
on payment.cusotmer_id=customer.customer_id;
为什么?我不能理解。帮助
这就像一个内部联接,因为没有不匹配:
注意:可以在下面随意将val
列名称更改为customer_id
。结果是一样的。
WITH cte1 (id, val) AS (SELECT 1, 100 UNION SELECT 2, 100)
, cte2 (id, val) AS (SELECT 10, 100)
SELECT COUNT(*)
FROM cte1 LEFT JOIN cte2 ON cte1.val = cte2.val
;
和
WITH cte1 (id, val) AS (SELECT 1, 100 UNION SELECT 2, 100)
, cte2 (id, val) AS (SELECT 10, 100)
SELECT COUNT(*)
FROM cte2 LEFT JOIN cte1 ON cte1.val = cte2.val
;
将产生相同的计数(2(。
我认为真正的问题是选择哪一个作为锚定表。因为它将另一个放在上面并进行加法运算。这里没有一个客户付款,例如,当我们查看第一个付款表时,当我们在第一个付款表格上计数时,它会带来5个客户,因为结果是基于付款表格的。在另一种可能性中,当以客户表为基础时,它会带来六个结果。可以理解,您选择哪一个取决于基表。
--customer (count 6) , payment (5)
WITH
payment(customer_id,paid) AS (SELECT 1,100 UNION SELECT 2,200 UNION SELECT 3,300 UNION SELECT 4,400 UNION SELECT 5,500 )
,customer(customer_id) AS (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6)
SELECT COUNT(*) FROM payment py LEFT OUTER JOIN customer ct ON py.customer_id=ct.customer_id;
WITH
payment(customer_id,paid) AS (SELECT 1,100 UNION SELECT 2,200 UNION SELECT 3,300 UNION SELECT 4,400 UNION SELECT 5,500 )
,customer(customer_id) AS (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6)
SELECT COUNT(*) FROM customer ct LEFT OUTER JOIN payment py ON py.customer_id=ct.customer_id;