Set difference in SQL query

Question

I am trying to select approval entries

SELECT * 
FROM A 
WHERE 
  LEFT(B, 5) IN 
    (SELECT * FROM 
       (SELECT LEFT(A.B,5), COUNT(DISTINCT A.C) c_count 
        FROM A 
        GROUP BY LEFT(B,5)
       ) p1 
       WHERE p1.c_count = 1
     ) 
     AND C IN 
        (SELECT * FROM 
            (SELECT A.C , COUNT(DISTINCT LEFT(A.B,5)) b_count 
             FROM A 
             GROUP BY C
            ) p2 
          WHERE p2.b_count = 1)

which takes a long time to run ~ 15 seconds.

Is there a better way to write this SQL?

Answer 1

You do not need to return data from nested subqueries. I'm not sure if this will affect indexing, but it’s easier to read.

And EXISTS / JOIN is probably better than IMHO and then uses IN

SELECT * 
FROM
    A 
    JOIN
    (SELECT LEFT(B,5) AS b1
        FROM A 
        GROUP BY LEFT(B,5)
        HAVING COUNT(DISTINCT C) = 1
    ) t1 On LEFT(A.B, 5) = t1.b1
    JOIN
    (SELECT C AS C1
        FROM A 
        GROUP BY C
        HAVING COUNT(DISTINCT LEFT(B,5)) = 1
    ) t2 ON A.C = t2.c1

But you will need a computed column as marc_s said at least

And 2 indexes: one on (computed, C)and another on(C, computed)

Answer 2

Set Difference (A-B) SQL, . , A B, , A, B, A B ID. :

# (A-B)
SELECT DISTINCT A.* FROM (A LEFT OUTER JOIN B on A.ID=B.ID) WHERE B.ID IS NULL

- Jayaram Timsina.

Answer 3

, , , , , LEFT(B, 5) . , .

SQL Server, , , :

ALTER TABLE A
   ADD LeftB5 AS LEFT(B, 5) PERSISTED

CREATE NONCLUSTERED INDEX IX_LeftB5 ON dbo.A(LeftB5)

LeftB5 LEFT(B, 5) - GROUP BY.

, GROUP BY C - C?