From: --CELKO-- on
>> 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
FROM Table1
WHERE Table1.key1
IN ((SELECT key2 FROM Table2)
(SELECT key1 FROM Table1));
From: Dom A on
you can do this easily with a left outer join

Table2 b ON a.ID = b.ID

"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
> as:
> SELECT * FROM table1 WHERE NOT table1.key1 IN (SELECT table2.key2 FROM
> table2)
> 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.
First  |  Prev  | 
Pages: 1 2
Prev: CTE with multiple anchors
Next: Q;re Descriptive stats