SELECT NOT IN with multiple columns in subquery

Question

Regarding the statement below, sltrxid can exist as both ardoccrid and ardocdbid. I'm wanting to know how to include both in the NOT IN subquery.

SELECT *
FROM glsltransaction A
    INNER JOIN cocustomer B ON A.acctid = B.customerid
WHERE sltrxstate = 4
    AND araccttype = 1
    AND sltrxid NOT IN(
    SELECT ardoccrid,ardocdbid
    FROM arapplyitem)

score 3 · Accepted Answer · answered Dec 08 '20 at 22:36

I would recommend not exists:

SELECT *
FROM glsltransaction t
INNER JOIN cocustomer c ON c.customerid = t.acctid
WHERE 
    ??.sltrxstate = 4
    AND ??.araccttype = 1
    AND NOT EXISTS (
        SELECT 1
        FROM arapplyitem a
        WHERE ??.sltrxid IN (a.ardoccrid, a.ardocdbid)
    )

Note that I changed the table aliases to things that are more meaningful. I would strongly recommend prefixing the column names with the table they belong to, so the query is unambiguous - in absence of any indication, I represented this as ?? in the query.

IN sometimes optimize poorly. There are situations where two subqueries are more efficient:

SELECT *
FROM glsltransaction t
INNER JOIN cocustomer c ON c.customerid = t.acctid
WHERE 
    ??.sltrxstate = 4
    AND ??.araccttype = 1
    AND NOT EXISTS (
        SELECT 1
        FROM arapplyitem a
        WHERE ??.sltrxid = a.ardoccrid
    )
    AND NOT EXISTS (
        SELECT 1
        FROM arapplyitem a
        WHERE ??.sltrxid = a.ardocdbid
    )

Thanks for the help! This did it. – Chris Dec 08 '20 at 22:46 — Chris, Dec 08 '20 at 22:46

SELECT NOT IN with multiple columns in subquery

1 Answers1