Is there a way to rewrite the SQL query efficiently
- by user320587
hi,
I have two tables with following definition
TableA TableB
ID1 ID2 ID3 Value1 Value ID1 Value1
C1 P1 S1 S1
C1 P1 S2 S2
C1 P1 S3 S3
C1 P1 S5 S4
S5
The values are just examples in the table. TableA has a clustered primary key ID1, ID2 & ID3 and TableB has p.k. ID1
I need to create a table that has the missing records in TableA based on TableB
The select query I am trying to create should give the following output
C1 P1 S4
To do this, I have the following SQL query
SELECT
DISTINCT TableA.ID1, TableA.ID2, TableB.ID1
FROM TableA a, TableB b
WHERE TableB.ID1 NOT IN
(
SELECT DISTINCT [ID3]
FROM TableA aa
WHERE a.ID1 == aa.ID1
AND a.ID2 == aa.ID2
)
Though this query works, it performs poorly and my final TableA may have upto 1M records.
is there a way to rewrite this more efficiently.
Thanks for any help,
Javid
