Re: including 'is null' brings cartesian result

drdave_at_canoemail.com wrote:

>Sorry if this is not the group for pl/sql questions but here goes..
>
>I have a query that works fine but does not give me all the records I
>need due to 1 of the columns allowing null. When I include the join
>statement and include 'or columname' is null the result is a cartesian
>product.
>
>here is an abbreviated cross view of the data and columnames..
>
>Minimum_wage table
>
>id amount effective_date mw_ind jurisdiction_id variation_id
>1, 1.25, 7/1/1965, 1, 1, null,
>2, 1.65, 7/1/1970, 0, 1, null,
>3, 1.75, 7/1/1971, 0, 1, 87,
>4, 1.75, 7/1/1971, 0, 1, 88,
>
>
>MINIMUM_WAGE_VARIATION
>minimum_wage_variation_id englishtext
>87 blah
>88 blah blah
>
>So when I run the following query I get the non-null values only
>
>SELECT mw.minimum_wage_amount, mw.effective_date, mw.prov_prov_id,
>mw.mwv_minimum_wage_variation_id,
>mwv.minimum_wage_variation_id,mwv.english_text AS variationname
>FROM MINIMUM_WAGE mw,MINIMUM_WAGE_VARIATION mwv
>WHERE mw.mwv_minimum_wage_variation_id = mwv.minimum_wage_variation_id
>AND mw.expiry_date is null
>AND mw.minimum_wage_ind = 1
>AND mw.jt_jurisdiction_type_id = 2
>
>
>however when I add the is null I get 2800 records instead of the 36 I
>expect..
>
>SELECT mw.minimum_wage_amount, mw.effective_date, mw.prov_prov_id,
>mw.mwv_minimum_wage_variation_id,
>mwv.minimum_wage_variation_id,mwv.english_text AS variationname
>FROM MINIMUM_WAGE mw,MINIMUM_WAGE_VARIATION mwv
>WHERE mw.mwv_minimum_wage_variation_id = mwv.minimum_wage_variation_id
>OR (mw.mwv_minimum_wage_variation_id is NULL)
>AND mw.expiry_date is null
>AND mw.minimum_wage_ind = 1
>AND mw.jt_jurisdiction_type_id = 2
>
>
>What am I missing???
>
>thx.
>
>Dave

Your second query uses 2 criteria and the  OR mw.mwv_minimum_wage_variation_id is NULL

Causes all records where mw.mwv_minimum_wage_variation_id is NULL to be returned without having any criteria applied to mvw - hence Cartesian Received on Wed Mar 02 2005 - 13:54:47 CST