EXISTS with IN returning all rows

Mirithu Kinyanjui 21

Hi,
I have two tables Table_A and Table_B, both with very many rows. I need data from both so I am using a JOIN and then filtering on column TXNCODE and DVAL in Table_B. Column DVAL has values ONE,TWO,THREE, FOUR,FIVE,SIX and I need only ONE,TWO,THREE,FOUR. I am also filtering where TXNCODE = TX.

I have tried using an IN clause but it is running for a very long time. If I use the EXISTS clause with IN, the TXNCODE filter works (gives only where TXNCODE=TX) but the DVAL returns everything.

Below is the query. Where am I going wrong?

SELECT
    TA.ID,
    TA.DDATE,
    TA.ACCOUNT,
    SUM(TB.AMOUNT) AS TOTAL_AMOUNT,
    TB.TXNCODE,
    TB.DVAL
FROM TABLE_A TA
INNER JOIN TABLE_B TB ON TA.ID = TB.ID
WHERE
    TA.DDATE = @SDATE
AND
    TB.TXNCODE = 'TX'
AND
    EXISTS (
        SELECT 1
        FROM TABLE_B TB2
        WHERE TA.ID = TB2.ID
        AND TB2.DVAL IN ('ONE', 'TWO', 'THREE', 'FOUR')
    )
GROUP BY
    TA.ID,
    TA.DDATE,
    TA.ACCOUNT,
    TB.TXNCODE,
    TB.DVAL;

Olaf Helper 43,246 Reputation points

2024-02-09T09:08:13.0233333+00:00

That's really to less on information. Please post table design as DDL, some sample data as DML statement and the expected result. And if you have code, then please post it as code block, not as unreadable text.

Erland Sommarskog 107.2K

If the query with EXISTS gives the wrong result don't use it. Does this query give the correct result:

SELECT
    TA.ID,
    TA.DDATE,
    TA.ACCOUNT,
    SUM(TB.AMOUNT) AS TOTAL_AMOUNT,
    TB.TXNCODE,
    TB.DVAL
FROM TABLE_A TA
INNER JOIN TABLE_B TB ON TA.ID = TB.ID
WHERE
    TA.DDATE = @SDATE
AND
    TB.TXNCODE = 'TX'
AND TB.DVAL IN ('ONE', 'TWO', 'THREE', 'FOUR')
GROUP BY
    TA.ID,
    TA.DDATE,
    TA.ACCOUNT,
    TB.TXNCODE,
    TB.DVAL;

Never mind, it is slow. We need to know first that the query is correct, else

Mirithu Kinyanjui 21 Reputation points

2024-02-14T17:22:17.0433333+00:00

@Erland Sommarskog Thank you for replying and I apologize for the tardy response.
Yes, the query as you have written it gives the correct result but is extremely slow. It the reason I was trying to use EXISTS, but even though it performed better, the results were wrong.
Erland Sommarskog 107.2K Reputation points

2024-02-14T21:57:27.95+00:00

Correctness trumps performance. Every time.

Anyway, it would help to see the actual query plan for the query. You can share this on https://www.pastetheplan.com.

It would also help to see the CREATE TABLE and CREATE INDEX statements for the tables.

Share via

EXISTS with IN returning all rows