Re: ???? SQL question ????

A join is generally more efficient than a subquery when possible. Try this:

SELECT Id, Id2, Id3
FROM table1 a, table2 b
WHERE a.id1||a.id2||a.id3 =

    b.table1_id1||b.table1_id2||b.table1.id3(+) AND b.table1_id2 is null
;

OR to conserve temporary disk space in the case of a large table1, try:

SELECT Id1, Id2, Id3
FROM table1 a
WHERE NOT EXISTS (SELECT 0 FROM table2

		WHERE a.id1||a.id2||a.id3 =
    		b.table1_id1||b.table1_id2||b.table1.id3)

Joost Ouwerkerk
Development Analyst
HSC Foundation, Toronto, Canada.

On Tue, 13 Jan 1998 13:37:38 +0100, ilepper_at_fac.fbk.eur.nl (ilepper) wrote:

>Ok here's a nice SQL problem, who can solve this one?
>
>I have got 2 tables
>
>Table 1 :
>prim. key : Id1, Id2, Id3
>
>Table 2 :
>prim. key : Id
>for. key : Table1_id1, Table1_id2, Table1_id3
>
>I want to do the following : I would like all records from table 1 where
>there's no record for it in table 2.
>
>Usualy it would look something like this :
>select * from table 1 where id not in (select id from table 2)
>but now table one has more attributes forming the prim. key.
>
>How can it be done, anyone?
>
>Thanks anyway for trying,
>by the way, I am using Orcale version 7, and Delphi 3 to excute the SQL
>query.
>
>Ido de Lepper
>ilepper_at_fac.fbk.eur.nl
Received on Tue Jan 13 1998 - 00:00:00 CST