From: --CELKO-- on 7 Mar 2010 20:42
>> There is a simple task - to return the records [sic: rows are not records] from Table1 where the key Table1.key1 doesn't exist in the Table2.
SELECT Table1.* -- never use * in production code
IN ((SELECT key2 FROM Table2)
(SELECT key1 FROM Table1));
From: Dom A on 8 Mar 2010 03:36
you can do this easily with a left outer join
Table1 a LEFT OUTER JOIN
Table2 b ON a.ID = b.ID
b.ID IS NULL
"Just D." <no(a)spam.please> wrote in message
> There is a simple task - to return the records from table1 where the key
> table1.key1 doesn't exist in the table2. The first idea was to write it
> SELECT * FROM table1 WHERE NOT table1.key1 IN (SELECT table2.key2 FROM
> Simple, but it's way too inefficient for sure. Is there some trick that
> I'm missing, something like an opposite to INNER JOIN? I doubt it exists,
> but who knows...
> There were two other ideas - to use a status column and change the status
> when the record was sent to the table2, using this column in the query,
> but for some reason my boss doesn'tlike this idea, explaining that we'll
> need to restructure the whole table, change the related logic, table is
> huge, any database structure corrections are painful, since the database
> is around 300 GBytes now and keeps growing, etc. I tried to explain that
> scannign the same table again and again is not the best way, but... Adding
> another table where we could track this status was also aworking idea, but
> it was put on hold for a while.
> Any other ideas?
> Just D.