SQL outer joins -> Oracle (+) vs outer(Informix)

Path: emory!samsung!zaphod.mps.ohio-state.edu!think.com!spool.mu.edu!cs.umn.edu!msi.umn.edu!math.fu-berlin.de!unido!mcshh!abqhh!oops!deerwood!georg From: georg@deerwood.zigzag.hanse.de (Georg Rehfeld) Newsgroups: comp.databases,comp.databases.informix Keywords: sql oracle informix outer join Message-ID: <yD2JaB1w164w@deerwood.zigzag.hanse.de> Date: 3 Nov 91 18:45:30 GMT Reply-To: georg@deerwood.zigzag.hanse.de Organization: Schorses Waffeleisen Cc: georg Hi net(t) SQL gurus, time for me to drop a question, I just fell into one of the SQL traps: after working on a SELECT for several hours and discussing the problem with 4 persons without a solution (for Oracle) I am ashamed but helpless enough to present this 5 lines of code to the world. Tables in question: Job: ---- +--------+ drv_key, Show ALL drivers, that means: | driver | name, if (driver used some 'red' cars) +--------+ ... show repeated drivers with all car.plates | else /|\\ show the driver with empty plate field (NULL) +------+ drv_key, < used(by) > car_key Informix solution: +------+ ------------------ \\|/ select driver.name, car.plate | from driver, OUTER(used, car) +--------+ car_key, where driver.drv_key = used.drv_key | car | plate, and used.car_key = car.car_key +--------+ color, ... and car.color = 'red' What Informix does is: join table 'used' to table 'car', apply the predicate 'and car.color = 'red'' to this partial join, with the resulting table do an outer join to 'driver', that gives an empty (NULL) row and therefore plate field for all drivers, that never used a red car, but does not suppress the driver rows. [other variants of the from clause as 'from driver, outer(used, outer(car))' give different results]. With Oracle you can write (the best we found till now): select driver.name, car.plate from driver, used, car where driver.drv_key = used.drv_key (+) and used.car_key = car.car_key (+) and (car.color = 'red' or car.color is null) The problem is, Oracle performs the complete join first and applys the predicate 'and car.color = 'red'' last. For drivers, that used one ore more red cars, there will be rows in the output table showing the plate; for drivers, that never used any car, the output table contains a row with an empty plate field, but drivers, that used some car of other colors are missing from the output. There seems to be no way to structure the join process as with Informix. Assume the following contents of the tables: driver used car - ------ - - - ----- ------ a Alf a g b HH-B1 blue b Bert a q g HH-G1 green c Charly a r q HH-R1 red d Dan c y r HH-R2 red d r y HH-Y1 yellow Output should/would be: Alf HH-R1 Alf HH-R2 Bert Charly <--- this row misses from Oracle output Dan HH-R2 The 'Charly' row misses, because Oracle performs the join from 'driver(c,Charly)' to 'used(c,y)' and the join from there to 'car(y,HH-Y1,yellow)' and then deleting the complete row, because color is not red. The 'Bert' row is shown, as Oracle inserts a virtual NULL row into 'used' when performing the outer join from 'driver' to 'used' and for this row inserts a virtual NULL row into 'car' while joining 'used' to 'car', the row will not be deleted because of the 'or car.color is null' predicate. 1) urgent: HOW may I do the job with Oracle? (See PS) (please mail+crosspost) 2) discuss: Am I right stating Informix SQL syntax for outer joins is much more flexible than Oracles syntax? Am I right, that with Oracle some jobs can't be done, that are possible with Informix? What says ANSI SQL? Thanks in advance for your help, if you mail me, I will summarize. Georg from deep inside a database PS: I know a solution for Oracle, that uses 'union', but this solution is unusable, because the neccessary 'order by' clause (not shown here) with unions allows only positional arguments (order by 3 asc, 1 desc); our environment generates order by clauses automagically, but with column names, we had to do 3 month of work (min) to really parse SQL and rephrase the order by. ___ ___ | + | |__ ' Georg Rehfeld, D-2000 Hamburg 26, Jordanstr. 26, 049 40 2518356 |_|_\\ |___, georg@deerwood.zigzag.hanse.de, ....!unido!mcshh!deerwood!georg An expert is a person who avoids the small errors