Question on Select

[Alex]

Hi,
I have two tables,
Table A: item_id
Table B: item_id, ref_code
and i want to list all ref_codes in table B that are not referenced by
Table A.
Table A has about 3million records./ table B 200

What is the best way to do that ?

Thanks
Alex



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Rajesh Kumar Mallah]

Re: Question on Select





select ref_code from tab_b where not exists (select * from tab_a where
item_id = tab_b.item_id);

make sure u have index on item_id in tab_a .

the above sql is quite efficient , other method is to use left join.


Alex wrote:
[color=blue]
> Hi,
> I have two tables,
> Table A: item_id
> Table B: item_id, ref_code
> and i want to list all ref_codes in table B that are not referenced
> by Table A.
> Table A has about 3million records./ table B 200
>
> What is the best way to do that ?
>
> Thanks
> Alex
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postg resql.org so that your
> message can get through to the mailing list cleanly[/color]




---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html