NULL in WHERE CLAUSE

Question

NULL in WHERE CLAUSE

eseosaoregie

SSCertifiable

Points: 6889
More actions
February 17, 2009 at 7:28 am

#134822

I am currently going through some of my predecesors code and came across something that does not make sense. I have included the code below:
SELECT
A.providerCode,
A.practiceCode,
A.POD,
A.activityMonth,
0,
0,
A.activity,
A.cost,
A.highCostRiskPool,
0
FROM
PBC_Budget_v_Actuals BvA RIGHT OUTER JOIN
vw_PBC_Report_Actuals A ON BvA.providerCode = A.providerCode AND BvA.practiceCode = A.practiceCode AND BvA.POD = A.POD AND
BvA.activityMonth = A.activityMonth AND BvA.highCostRiskPool = A.highCostRiskPool
WHERE
BvA.providerCode IS NULL
I do not understamd the significance of the Null in the where clause. The join is based on BvA.providerCode = A.providerCode etc. IF BvA.providerCode IS NULL then how can the join criteria work.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Because it is a RIGHT OUTER JOIN the WHERE clause is saying give me all the rows in vw_PBC_Report_Actuals that do NOT have a corresponding row in PBC_Budget_v_Actuals. This limits the output to only missing rows.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 2

eseosaoregie (2/17/2009)
I am currently going through some of my predecesors code and came across something that does not make sense. I have included the code below:
SELECT
... FROM
PBC_Budget_v_Actuals BvA
RIGHT OUTER JOIN
vw_PBC_Report_Actuals A
ON BvA.providerCode = A.providerCode
AND BvA.practiceCode = A.practiceCode
AND BvA.POD = A.POD
AND BvA.activityMonth = A.activityMonth
AND BvA.highCostRiskPool = A.highCostRiskPool
WHERE
BvA.providerCode IS NULL
I do not understamd the significance of the Null in the where clause. The join is based on BvA.providerCode = A.providerCode etc. IF BvA.providerCode IS NULL then how can the join criteria work.

The query has a right outer join so all [A] records will be included even if no matching [BvA] record is encountered. The NULL criteria then filters the result set to only include [A] records with no matching [BvA] records.

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 3

eseosaoregie

SSCertifiable

Points: 6889

February 17, 2009 at 8:12 am

#945224

Thanks. I forgot that it was a RIGHT outer join.