Skip to content
Advertisement

SELECT NOT IN with multiple columns in subquery

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)

Advertisement

Answer

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
    )
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement