Have Geek Brain

Is there a way to efficiently rewrite the SQL query

I have two tables with the 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. Table A has clustered primary key ID1, ID2 and ID3, and TableB has pk ID1 I need to create a table with missing records in TableA based on TableB The select query I am trying to create should contain the following output

C1  P1  S4

For 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 
    )

Even though this query works, it works poorly and my final TableA can have up to 1M records. is there any way to rewrite this more efficiently.

Thanks for any help Javid

+2


a source to share


3 answers


Try the following:



SELECT T1.ID1, T1.ID2, T2.ID1 FROM (
    SELECT ID1, ID2
    FROM TableA
    GROUP BY ID1, ID2
) T1
CROSS JOIN TableB T2
LEFT JOIN TableA T3
ON T1.ID1 = T3.ID1 AND T1.ID2 = T3.ID2 AND T2.ID1 = T3.ID3
WHERE T3.ID1 IS NULL
+1


a source


Assuming TableA is a joinery table, you need to create a list of possible values ​​with a cross join, which might kill you anyway ...

select TableC.ID1 as C, TableP.ID1 as P, TableB.ID1 as B
from TableC 
    cross join TableP
    cross join TableB

(It's a big guess that these are relatively small tables)



Then you can join this request like so:

select ID1, ID2, ID3
from TableA A
    left outer join 
        (select TableC.ID1 as C, TableP.ID1 as P, TableB.ID1 as B
         from TableC 
             cross join TableP
             cross join TableB) X on A.ID1 = X.C and A.ID2 = X.P and A.ID3 = X.B
where X.ID3 is null
0


a source


Thanks to everyone who shares your thoughts. I removed the DISTINCT keyword from the INNER select query and was able to get some performance. Also, I changed the query and got better performance. I decided to share my solution with everyone

Select c.*
FROM TAbleA a
RIGHT JOIN
( SELECT DISTINCT ID1, ID2, b.ID1 
  FROM TableA, 
  ( SELECT ID1 FROM TableB ) b 
) c
ON a.[ID1] = c.[ID1] AND a.[ID2] = c.[ID2] AND a.[ID3] = c.[ID3]
WHERE a.[ID3] IS NULL
0


a source






More articles:


All Articles