Why does CASE in WHERE clause return different results than when using AND/OR operators instead?-CodePudding

I have two queries. Below is query #1, which uses CASE in the WHERE clause:

SELECT
    customer.id AS "Account",
    customer.country AS "Country",
    (
        CASE
            WHEN customer.country = 'US' THEN customer.state
        END
    ) AS "State",
    TO_CHAR(i.datepaid, 'Month') AS "Month",
    ROUND((SUM(i.subtotal - i.credit)):: NUMERIC, 2) AS "Sales",
    ROUND((SUM(i.tax)):: NUMERIC, 2) AS "Tax",
    ROUND(
        SUM((i.subtotal - i.credit   i.tax) - refunded_amount):: NUMERIC,
        2
    ) AS "Gross"
FROM
    invoices i
    JOIN customer ON i.customer_id = customer.id
    JOIN (
        SELECT
            ii.invoices_id,
            SUM(ii.refunded_amount) AS refunded_amount
        FROM
            invoices i
            JOIN customer ON i.customer_id = customer.id
            JOIN invoice_items ii ON i.id = ii.invoices_id
        WHERE
            i.status = 'Paid'
            AND i.datepaid BETWEEN '2015-01-01' AND '2015-02-01'
            AND customer.billing_day <> 0
            AND customer.register_date < '2015-02-01'
            AND customer.account_exempt = 'f'
            AND customer.country <> ''
        GROUP BY
            ii.invoices_id
    ) ii ON i.id = ii.invoices_id
WHERE
    i.status = 'Paid'
    AND i.datepaid BETWEEN '2015-01-01' AND '2015-02-01'
    AND customer.billing_day <> 0
    AND customer.register_date < '2015-02-01'
    AND customer.account_exempt = 'f'
    AND customer.country <> ''
    AND (
        CASE
            WHEN customer.country = 'US' THEN customer.tax_exempt <> 'f'
        END
    )
GROUP BY
    customer.id,
    TO_CHAR(i.datepaid, 'Month')
ORDER BY
    customer.country,
    (
        CASE
            WHEN customer.country = 'US' THEN customer.state
        END
    );

Below is query #2, which is the same as query #1 except it uses AND customer.country <> 'US' OR customer.tax_exempt <> 'f' in the place of the CASE.

SELECT
    customer.id AS "Account",
    customer.country AS "Country",
    (
        CASE
            WHEN customer.country = 'US' THEN customer.state
        END
    ) AS "State",
    TO_CHAR(i.datepaid, 'Month') AS "Month",
    ROUND((SUM(i.subtotal - i.credit)):: NUMERIC, 2) AS "Sales",
    ROUND((SUM(i.tax)):: NUMERIC, 2) AS "Tax",
    ROUND(
        SUM((i.subtotal - i.credit   i.tax) - refunded_amount):: NUMERIC,
        2
    ) AS "Gross"
FROM
    invoices i
    JOIN customer ON i.customer_id = customer.id
    JOIN (
        SELECT
            ii.invoices_id,
            SUM(ii.refunded_amount) AS refunded_amount
        FROM
            invoices i
            JOIN customer ON i.customer_id = customer.id
            JOIN invoice_items ii ON i.id = ii.invoices_id
        WHERE
            i.status = 'Paid'
            AND i.datepaid BETWEEN '2015-01-01' AND '2015-02-01'
            AND customer.billing_day <> 0
            AND customer.register_date < '2015-02-01'
            AND customer.account_exempt = 'f'
            AND customer.country <> ''
        GROUP BY
            ii.invoices_id
    ) ii ON i.id = ii.invoices_id
WHERE
    i.status = 'Paid'
    AND i.datepaid BETWEEN '2015-01-01' AND '2015-02-01'
    AND customer.billing_day <> 0
    AND customer.register_date < '2015-02-01'
    AND customer.account_exempt = 'f'
    AND customer.country <> ''
    AND customer.country <> 'US'
    OR customer.tax_exempt <> 'f'
GROUP BY
    customer.id,
    TO_CHAR(i.datepaid, 'Month')
ORDER BY
    customer.country,
    (
        CASE
            WHEN customer.country = 'US' THEN customer.state
        END
    );

I expected these two queries to return the same results. However, query #1 only returns rows for customers in the US, while query #2 returns the same rows as query #1, plus rows for customers from other countries. What's up with that?

CodePudding user response：

This is the difference between your #1 and #2:

#1:

(
    CASE
        WHEN customer.country = 'US' THEN customer.tax_exempt <> 'f'
    END
)

#2:

 customer.country <> 'US' OR customer.tax_exempt <> 'f'

Straightaway, I can see these are not semantically similar and that would explain how the two could produce different output. You can demonstrate how these are not the same with a simpler statement:

The equivalent of your #1:

select 1 where CASE WHEN 'A' = 'US' THEN 'B' <> 'f' END

The equivalent of your #2:

select 1 where 'A' <> 'US' or 'B' <> 'f'

Of course, all you have to do is modify A and B to test the other states to see where you "went south".

TLDR; You replaced the = operator with <> and you incorrectly read the case predicate as "this or that" instead of "this and that".