Case When and or Statement producing different results

Question

I have a count statement, I want one condition to be equal and two not equal. If I query country and user_tag separately, I get 0 rows but If I query same time like below I get 10 rows.

What am I doing wrong

COUNT(DISTINCT CASE WHEN 
    n.country != o.country 
    OR n.user_tag != o.user_tag
    AND n.email_address = o.email_address THEN n.email_address
    END) AS OTHER_CHANGES_IN_DATA
FROM OLD o
INNER JOIN NEW n 
    ON n.user_id = o.user_id

If both country and user_tag is ran like this the results should be 0

Please update your question with sample data and the result you want to achieve, based on that data — NickW, Mar 27 '23 at 20:26
you are counting email, so count teh email in old and new and what your join makes it takes every userid fromold and combines it with every userid id new, that can make a lot of unwanted columns — nbk, Mar 27 '23 at 20:34

score 0 · Accepted Answer · answered Mar 27 '23 at 20:32

Your question not quite clear, so I'm assuming you mean that both

COUNT(DISTINCT CASE WHEN 
    n.user_tag != o.user_tag
    AND n.email_address = o.email_address THEN n.email_address
    END) AS OTHER_CHANGES_IN_DATA
FROM OLD o
INNER JOIN NEW n 
    ON n.user_id = o.user_id

COUNT(DISTINCT CASE WHEN 
    n.country != o.country 
    AND n.email_address = o.email_address THEN n.email_address
    END) AS OTHER_CHANGES_IN_DATA
FROM OLD o
INNER JOIN NEW n 
    ON n.user_id = o.user_id

return 0, but query you cited in your question returns 10 for some user.

If my assumption correct, than I believe I can explain your problem: AND takes precedence over OR in your case statement. So conditions of this two query not really correlated to condition of yours. More about OR and AND in SQL here.

Regarding your query: try

... WHEN 
    (n.country != o.country 
    OR n.user_tag != o.user_tag)
    AND n.email_address = o.email_address THEN ...

Case When and or Statement producing different results

1 Answers1