What is the difference between using 'and' after Join and 'Where' Clause

Viewed 23

I was trying to solve this Market Analysis | Problem on LeetCode: https://leetcode.com/problems/market-analysis-i/

I wrote this query,

select 
    u.user_id 'buyer_id', 
    u.join_date 'join_date', 
    sum(case when o.order_id is not null then 1 else 0 end) as 'orders_in_2019'
    
    from Users u left join Orders o
    on u.user_id = o.buyer_id 
    where year(o.order_date) = 2019
    group by u.user_id

But, it didn't accept my query and gave me unexpected results, which were as follows,

Input Tables

{"headers":{"Users":["user_id","join_date","favorite_brand"],"Orders":["order_id","order_date","item_id","buyer_id","seller_id"],"Items":["item_id","item_brand"]},"rows":{"Users":[[1,"2018-01-01","Lenovo"],[2,"2018-02-09","Samsung"],[3,"2018-01-19","LG"],[4,"2018-05-21","HP"]],"Orders":[[1,"2019-08-01",4,1,2],[2,"2018-08-02",2,1,3],[3,"2019-08-03",3,2,3],[4,"2018-08-04",1,4,2],[5,"2018-08-04",1,3,4],[6,"2019-08-05",2,2,4]],"Items":[[1,"Samsung"],[2,"Lenovo"],[3,"LG"],[4,"HP"]]}}

My Output

{"headers": ["buyer_id", "join_date", "orders_in_2019"], "values": [[1, "2018-01-01", 1], [2, "2018-02-09", 2]]}

Expected Output

{"headers": ["buyer_id", "join_date", "orders_in_2019"], "values": [[1, "2018-01-01", 1], [2, "2018-02-09", 2], [3, "2018-01-19", 0], [4, "2018-05-21", 0]]}

Apparently, the issue with my query was just a simple where clause. If I change the following

where year(o.order_date) = 2019

to

and year(o.order_date) = 2019

The query works as expected. I don't understand how is it so. Can anyone explain please? From my understanding, where clause is also doing the same thing.

0 Answers
Related