user1921849 user1921849 - 4 months ago 17
SQL Question

LEFT Outer join +where clause unexpected results

I am surprised by the results of a query and am hoping someone can explain it. The query is kind of large, so I distilled it to the key part:

SELECT *
FROM A
INNER JOIN B ON A.id=B.id
LEFT JOIN C ON C.ID=B.ID
WHERE A.Dt='2016-06-23'
AND (B.StatusCode=' ' OR C.Code <> 9)


I am getting dates other than 6/23/16 even though I have it in my where clause. I think it has to do with combining columns in my where clause from a Left joined table and an inner joined table in one expression, but have never experienced this before.

UPDATE: Adding actual query

SELECT *
FROM CERT2.cube_mbbal_Daily_Balances BAL
INNER JOIN CERT.Dim_Account A
ON A.AccountKey = BAL.AccountKey
LEFT JOIN CERT2.Dim_LoanAccount LA
ON A.AccountKey = LA.AccountKey
WHERE
(A.AccountClassification IN ('Checking', 'Savings')
AND AccountStatus IN ('1', '3', '5', '6', '7'))
OR (A.AccountClassification IN ('Time')
AND AccountStatus IN ('1', '5', '7'))
OR (A.AccountClassification IN ('Loan')
AND (AccountStatus <> 'C'
OR RiskCode <> 9))
AND
BAL.DateOfFinancialMeasure=20160623

Answer

AND is evaluated before OR. It's like you're evaluating 2 + 5 + 7 * 5 and wondering why you're getting 42 and not 70.

Try:

select *
from CERT2.cube_mbbal_Daily_Balances BAL
inner join CERT.Dim_Account A
    on A.AccountKey = BAL.AccountKey
left join CERT2.Dim_LoanAccount LA
    on A.AccountKey = LA.AccountKey
where (
           (A.AccountClassification in ('Checking', 'Savings') and AccountStatus in ('1', '3', '5', '6', '7'))
        or (A.AccountClassification in ('Time') and AccountStatus in ('1', '5', '7'))
        or (A.AccountClassification in ('Loan') and (AccountStatus <> 'C' or RiskCode <> 9))
    )
    and BAL.DateOfFinancialMeasure = 20160623
Comments