I am new to SQL and learning joins, and I am trying to understand why the count is different in the two queries below:
select count(*)
from products p
left outer join order_details_v od
on p.product_id = od.order_item_product_id
This query returns a count value of 12332.
However, when I add an additional condition to the join in the second query:
select count(*)
from products p
left outer join order_details_v od
on p.product_id = od.order_item_product_id
and to_char(od.order_date::timestamp, 'yyyy-MM') = '2014-01'
The count value is 1224.
Why is the count different in the two queries? I understand that the additional condition in the join could filter out some rows, but since it is a left outer join, all the rows of the left table should be kept, leading to the same count value. Where am I wrong?
The count is different in the two queries because the additional condition in the second query filters out rows from the left table before performing the join. This means that only the rows from the left table that satisfy the additional condition are included in the result.
In the first query, all rows from the left table (products) are included in the result, regardless of whether there is a matching row in the right table (order_details_v). This is because it’s a left outer join, which means all rows from the left table are kept.
In the second query, the additional condition to_char(od.order_date::timestamp, 'yyyy-MM') = '2014-01' is applied to the join. This condition filters out rows from the right table (order_details_v) that don’t satisfy the condition. Since it’s a left outer join, the rows from the left table (products) are still included in the result, but only if there is a matching row in the right table that also satisfies the additional condition.
Therefore, the count is different because the second query only counts the rows that satisfy both the join condition and the additional condition, while the first query counts all rows from the left table, regardless of the join condition.