can 1 replace null

[dodge20]

I have a query that I can't quite get to work correctly in Informix, but it works fine in oracle. The problem is, it says null isn't in any table in any column, so I was wondering if I could change the word null to 1 (I think that is the value of null in informix). Or will that be the equivalent to row 1? Any ideas.

select * from awj_shop a
where not exists (select null from tcc_shop t
where t.name = a.name and t.address = a.address
and t.zip = a.zip)

Replace with

select * from awj_shop a
where not exists (select 1 from tcc_shop t
where t.name = a.name and t.address = a.address
and t.zip = a.zip)

Dodge20

 

[olded]

Dodge:

In Informix, null is undefined, and is not the name of any column in the database. If I modify your select slightly:

# untested
select * from awj_shop a
where not exists (select * from tcc_shop t
where t.name = a.name and t.address = a.address
and t.zip = a.zip)

returns the set of records from awj_shop that do not exist in tcc_shop table. Any columns that are null are ignored. I'm not certain, but I think you're trying to include a null comparision on one or more of the columns. If so, you need to explicitly mention null.

Regards,


Ed

 

[dodge20]

Ed thanks for your response. After some testing, I found out that it doesn't matter what I put in the not exists select statement, because the exists eithe returns true or false, so therefore it doesn't matter what I put in.

Thanks for your input

Dodge20

 

[mberni]

well it is not of any concern in this context, but a

... where not exists (select * from table) ...
will perform slower than a

... where not exists (select 1 from table) ...

especially if table has very long records

by the way:

in oracle there is a dirty (and much faster) trick to avoid "not exists":

just make an "outer join" on the table from the not exist and include a "notexisttable.anycolumn IS NULL" in your where statement. so you will get the rows where there is no match. by this the engine will use indexes much more efficiently.

i tried this one in informix but never succeeded.

maybe some more often seen posters have some ideas