Re: Problem: SE 5.06, outer joins and nulls

surf@bonsai.com wrote: :I'm trying to do an outer join between two tables, say: : :domtable(id, base_value) :subtable(id, current_value) : :where a subtable record is created when the base_value in the domtable :is no longer valid as a current value. : :my query runs like so: : :select domtable.id, domtable.base_value :from domtable, outer subtable :where subtable.current_value is null :and domtable.id = subtable.id : :One problem is that the above query returns all rows driven by :domtable regardless if the row's counterpart in subtable has a null :value in current_value or not. This is correct behaviour to the extent your description is acutally correct. Think of it like this: :where subtable.current_value is null creates a subset of the subtable that is considdered for the join. Every row in domtable that has a corresponding id value in this subset of subtable is joined regularly and returned by your select. If the subtable id and current_value had been included in your select their value would have been read from subtable. Because of the outer join spesification every other row of domtable is also returned. If the subtable id and current_value had been included in your select their value would have been set to null as standard for an outer join. When you do an outer join like this every row of domtable that satisfies anything you write in the where clause on domtable alone will allways be returned. Where spesifications on the outer table (subtable above) will only serve to eliminate columns in that table as candidates to be joined. It's not usefull unless you return some columns from the outer table and use those for some purpose. In your case it has no value as you have found out. I believe this behaviour is correct and it sure is usefull. I have needed it several times. Now what you seem to want to have returned is all rows in domtable that have a corresponding row (same id value) in subtable and where subtable.current_value is null. You wouldn't need an outer for that, so you must want some more rows. I can only guess that you want those rows from domtable that doesn't have a corresponding row in subtable at all no matter what the content of current_value is. We need an example: domtable subtable id base_value id current_value A 10 B 10 B 11 C 15 C null D 20 D 25 D null E 30 E null If we modify your select to include both domtable.id and subtable.id it is clearer what gets returned: select domtable.id, domtable.base_value, subtable.id sid from domtable, outer subtable where subtable.current_value is null and domtable.id = subtable.id id base_value sid Reason for value of sid: A 10 null <-A doesn't exist in subtable B 10 null <-B 11 is removed by where C 15 C <-C null exists - joined D 20 D <-D null exists - joined E 30 E <-E null exists - joined If you removed "subtable.current_values is null" from your where clause another row would be returned as well: D 20 D <-D because the row in subtable with values D 25 is now not removed by the where clause. This is actually what you use such a spesification on the subtable in the where clause for. Some rows are actually removed from the result. Now what you probably want returned is: id base_value sid Reason why you want this row: A 10 null <-A doesn't exist in subtable C 15 C <-C null exists - joined D 20 D <-D null exists - joined E 30 E <-E null exists - joined The row that is eliminated is: B 10 null and should probably not be included because there is a row with values B 11 in the subtable and no row with id = B and current_value is null. If this is what you want you have to either find a way to use subqueries or do one select into a temporary table first and the final select from that. I don't have the time to write out that, but have the following explanation of general problems with joins and outer ones in particular: The following is a graph of two tables and their interesting parts for joining data: v---content of table A--v ------------|------------|------------ ^---content of table B--^ ^--Part 1--^ ^--Part 2---------------^ ^--Part 3----------------------------^ ^--Part 4-^ ^--Part 5--------------^ ^--Part 6-^ Part 4 is the result of a regular inner join. Part 2 and Part 5 are the results of A outer B and B outer A respectively. Part 3 is a full outer join which is not supported by Informix SQL. Part 1 we may have called "A outer only B" and Part 6 "B outer only A". There is no way to do this in a simple select in Informix. I have hardly ever needed to return Part 3 (the full outer join), although I could conceive of situations where it would be very usefull. To return Part 1 or Part 6 is a quite common need for me, and it's very sad it isn't directly supported. Some of these parts can be returned by using subqueries. I some cases this is ok, in other it is not due to performance. If the engine uses longer time to return data than you can possibly expect users to wait you have to say the feature that is used is not supported. The users would say so at least. The way we usually return Part 1 is to do a Part 2 join (regular outer join) first into a temporary table. Then do a select on this returning only rows with all nulls for values from table B. Part 6 we of course return in a similar way. The engine could have done this much faster and with much less use of resources if there had been a syntax to spesify it. : :Additionally, when I change the query slightly: : :select domtable.id, subtable.current_value :from domtable, outer subtable :where subtable.current_value is not null :and domtable.id = subtable.id : :both of these queries return the same set of domtable.id suggesting that :subtable.current_value is both null and not null for rows in the :result table where there was not a subtable record to correspond with the :domtable record driving the outer join. See above. This is the wrong description of the right behaviour. :From the Informix Guide to SQL, Tutorial, December 1991 on P 3-19 in the :section on Outer Joins, I see: : :"In an outer join, the result contains the combination of rows from the : tables that satisfy the join conditions [domtable.id = subtable.id]. : Rows from the dominant table that would otherwise be discarded are : preserved, even though no matching row was found in the subservient : table. The dominant table rows that do not have a matching subservient : table row receive a row of nulls before the selected columns are : projected." : :To make matters worse, these "not quite nulls" match every relational :operation I apply to the field that is supposed to be null (real :nulls fail these) : :Is this a known bug? I've seen some rumblings on here that indicate that :this might be, but I coul