Prev: CTE with multiple anchors
Next: Q;re Descriptive stats
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
FROM Table1
WHERE Table1.key1
IN ((SELECT key2 FROM Table2)
EXCEPT
(SELECT key1 FROM Table1));
From: Dom A on 8 Mar 2010 03:36
you can do this easily with a left outer join

SELECT
*
FROM
Table1 a LEFT OUTER JOIN
Table2 b ON a.ID = b.ID
WHERE
b.ID IS NULL

"Just D." <no(a)spam.please> wrote in message
news:O8mRjdCvKHA.3536(a)TK2MSFTNGP06.phx.gbl...
> 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