select count(*) result in a LEFT OUTER JOIN is same after switching tables , I can't understand-CodePudding

As I am learning left outer join, I came to the conclusion

A left outer join B = everything in A and common thing in B mirroring respective value in the result table, other values of A which don't have common values with B table, have a null value in B side.

So if A has 15 values, B has 29 values(5 commons), then the result of the following query will be 15. Or if A has 15 values, B has 10 values(5 commons) the result will be still 15.

select count(*) from
A left outer join B 
on A.name=B.name;

My Problem:

I have a dvdrental database. Customer table, Payment table. They have 599,14596 rows respectively.

When I run the query: (I expected 14,596 and got 14,596)

select count(*) from
payment left outer join customer
on payment.customer_id=customer.customer_id;

but when I switched tables i.e;( I expected 599 but getting 14,596)

select count(*) from
customer left outer join payment
on payment.cusotmer_id=customer.customer_id;

why? I can't understand. Help

CodePudding user response：

It's just like an inner join, since there are no non-matches:

Note: Feel free to change the val column name to customer_id in the following. The result will be the same.

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
;

and

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
;

will produce the same count (2).

CodePudding user response：

I think the real issue is which one you choose as the anchor table. because it places the other on top of it and does the addition. There is not one customer payment here, for example, when we look at the first payment table, when we put count on the first payment table, it will bring 5 customers because the result is based on the payment table. In the other possibility, when the customers' table is based, it brings six results. As it can be understood, which one you choose depends on the base table.

--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;