Re: What should the following query return

Shubhasheesh Anand <anand@jwala.engr.sgi.com> wrote: :Hi, : :I have a query that does not seem to behave the way I :think it should !! : :Please see if you can help in pointing out the problem. : :I have the following schema: : :CREATE TABLE tb1 :( : license_no INT NOT NULL, : name VARCHAR(250) NOT NULL :); : :CREATE TABLE tb2 :( : license_no INT NOT NULL, : country VARCHAR(100) NOT NULL, : state VARCHAR(100) NOT NULL :); : : :And I insert the following data: : :INSERT INTO tb1 values ( 1, 'Hen' ); : :SELECT t1.license_no :from tb1 t1 , tb2 t2 :where t1.name = 'Hen' or ( t1.license_no = t2.license_no and t2.country := :'Mars' and t2.state = 'Colorado' ) : :The above query does not return anything, though I think it :should return the row in tb1. Even if you remove the "or (..." from the where clause it will return nothing. This statement creates a cartesian join between tb1 and tb2. As there are no rows in tb2 it will return nothing. It's generally an error to create a cartesian join. What you seem to want to do here is a what I would call a "full outer join". There is no direct support for that in any Informix database. What might solve this would be something like: SELECT t1.license_no, t1.name, t2.license_no, t2.country, t2.state from tb1 t1, outer tb2 t2 where t1.license_no = t2.license_no and t1.name = 'Hen' union SELECT t1.license_no, t1.name, t2.license_no, t2.country, t2.state from tb2 t2, outer tb1 t1 where t1.license_no = t2.license_no and t2.country = 'Mars' and t2.state = 'Colorado' Two outer joins are done, one each way. I have added more columns to the select as this would normally not be very meaningfull if all you returned was t1.license_no. You might be able to increase performance by saying "union all" as that might remove an extra creation of a temporary table to remove duplicates. I haven't thought through the consequenses of either alternative. : :Subsequently, if I insert the following: : :INSERT INTO tb2 values (3, 'Jupiter', 'Kansas'); : :The same query DOES correctly return the row: : :license_no : 1 The cartesian join now created one row containing: 1, Hen, 3, Jupiter, Kansas your projection (the select t1.license_no) returned the 1 as the where clause now is true due to the Hen from t1.name : :Now if I insert the following : :INSERT INTO tb2 values (4, 'Saturn', 'Texas' ); : :and run the same query, I get the same row, but twice: : :license_no : 1 : 1 : :Though I think it should return it only once. No, the cartesian join now creates two rows: 1, Hen, 3, Jupiter, Kansas 1, Hen, 4, Saturn, Texas which both fullfills your where clause. Allways try a select * when you get this kind of problem and you will quite often see the problem immediately. :Thanks :Shubhasheesh Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org