Re: Outer join question

-----Original Message----- From: ccremid <ccremid@saadev.saa.noaa.gov> To: Guillermo Labatte <labatteg@usa.net> Date: Viernes 13 de Febrero de 1998 10:49 Subject: Re: Outer join question > > >Guillermo Labatte wrote: > >> Hi, >> I'm using Online 5.07 >> I issue the command ... >> >> select * from table_A,outer table_B >> where table_A.x=table_B.y >> into temp table_C; >> select * from table_C where y is null>> >> and it returns a different result from >> >> select * from table_A,outer table_B >> where table_A.x=table_B.y and >> table_B is null;>> >> Why? > > HI: > >You have hit the three way logic involved when dealing with null >values. >besides this the two selects are different. > >Just to make the point clear lets assume that you do not have nulls on >the table_A or table_B. > >In the first query you are selecting all the values from table_a that >are not present on table_b. >thats it. > >On the second query (assuming that your condition ...and table_b.y is >null is changed to >and table_b.y = "some_value") you are selecting all the values from >table_a and the rows >on table_b that have column y = "some_value". > >The same is true when working with nulls, but you will need to remember >the three way logic >involved when working with nulls. > >Hope this helps > >Tino. > Sorry, the last query should be "... and table_B.y is null" Yes, I wanted to retrieve all the values from table_a that do not exist in table_b. The first query works fine, but not the second. I don't actually have null values in neither table_a or table_b. However table_a.x accepts null values. What's the three way logic when working with nulls?