Re: SQL Query
Two similar, but different, solutions to the problem ---
>TABLE A TABLE B
>-----------------------
>project_no project_no
>________________________
> 1 1
> 2 -
> 3 3
[...]
> is there anyway I can do my query to find the project_no from TABLE A
>that is not in the TABLE B?
As several people have suggested, you should use a subquery.
There are two different ways of doing this...
Either:
select a.project_no from a
where a.project_no not in
(select b.project_no from b);
Under SQL 2.1 this will be slow if there are a large number of rows in the
tables - Informix performs the subquery on table b for every row in table a
being examined. If there are 1000 rows in each table, then there are 1000000
row fetches! 4.0 is supposed to be able to opitimise this better by performing
the subquery first, once only.
Or:
select a.project_no from a
where not exists
(select b.project_no from b
where b.project_no = a.project_no);
This is fast if there is an index on b.project_no, and should be used in
preference to the first if using SQL 2.1. There would be only 2000 row fetches
in this case.
>Thanks. Ruchi.
Hugh.