CODEWITHSUNDEEP
sqlsql-server
Quantbuff
Quantbuff

Reputation: 847

SQL Server query combining UNION and NOT EXISTS

I want to find records which do not exist in the UNION of two select statements but exist in table t1. This is how I am doing it. Is there a better way of doing this?

UPDATE t1
SET    t1.col1 = 0
WHERE  NOT EXISTS (
    SELECT tab2.col2
    FROM   tab2,
           tab3
    WHERE  NOT EXISTS (SELECT *
                       FROM   tab4,
                              tab5
                       WHERE  tab2.col3 = tab4.col3
                              AND tab4.col4 = tab5.col4
                              AND tab5.col5 IN ( 'TT', 'YY' ))
           AND tab3.col2 = tab2.col2
           AND tab2.col2 NOT IN(SELECT DISTINCT col2
                                FROM   tab2_uk WITH (nolock))
           AND t1.col2 = tab3.col2
           AND tab3.date IS NULL
           AND ( Isnull(tab2.pos, 0) > 0
                  OR Isnull(tab2.op, 0) > 0
                  OR Isnull(tab2.co, '-1') <> '-1' )
    UNION
    SELECT tab6.col2
    FROM   dbo.tab6 WITH (nolock),
           dbo.tab3 WITH (nolock)
    WHERE  NOT EXISTS (SELECT *
                       FROM   tab4,
                              tab5
                       WHERE  tab6.col3 = tab4.col3
                              AND tab4.col4 = tab5.col4
                              AND tab5.col5 IN ( 'TT', 'YY' ))
           AND t1.col2 = tab3.col2
           AND tab3.col2 = tab6.col2
           AND tab6.po > 0
           AND tab3.date IS NULL)

Upvotes: 0

Views: 8242

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

I want to find records which do not exist in the UNION of two select statements

Since the UNION only removes complete duplicates in both sub-queries, you can skip this (possibly expensive) step and just check with NOT EXISTS on each individual subquery. (NOT) EXISTS tends to be the fastest method to check for existence anyway.

I also rewrote your JOINs to modern-day ANSI syntax. Otherwise I left it unchanged:

UPDATE t1
SET    t1.col1 = 0
WHERE  NOT EXISTS (
     SELECT tab2.col2
     FROM   tab2
     JOIN   tab3 ON tab3.col2 = tab2.col2
     WHERE  NOT EXISTS (
        SELECT *
        FROM   tab4
        JOIN   tab5 ON tab5.col4 = tab4.col4
        WHERE  tab2.col3 = tab4.col3
        AND    tab5.col5 IN ('TT', 'YY')
        )
     AND tab2.col2 NOT IN(SELECT DISTINCT col2
                          FROM   tab2_uk WITH (nolock))
     AND tab3.col2 = t1.col2
     AND tab3.date IS NULL
     AND ( Isnull(tab2.pos, 0) > 0
        OR Isnull(tab2.op, 0) > 0
        OR Isnull(tab2.co, '-1') <> '-1' )
     )
AND  NOT EXISTS (
        SELECT tab6.col2
        FROM   dbo.tab6 WITH (nolock)
        JOIN   dbo.tab3 WITH (nolock) ON tab3.col2 = tab6.col2
        WHERE  NOT EXISTS (
           SELECT *
           FROM   tab4
           JOIN   tab5 ON tab5.col4 = tab4.col4
           WHERE  tab4.col3 = tab3.col3
           AND    tab5.col5 IN ('TT', 'YY')
           )
        AND tab6.po > 0
        AND tab3.col2 = t1.col2
        AND tab3.date IS NULL
        )

Upvotes: 4

Related Questions