How to rewrite SQL query without IN clause

Question

Schema:

Table A: AID(PK), RECEIVE_DATE
Table B: BID(PK), AID(FK), MESSAGE, ITEMID, ITEMTYPE

Tables A-to-B have a one-to-many mapping.

Here is a working SQL query (in SQL Server) to find out the latest message grouped by ITEMID i.e for different ITEMID (of ITEMTYPE say as 'XYZ').

 SELECT 
     b.MESSAGE, b.ITEMID
 from a
 inner join b on b.aid = a.aid AND b.ITEMTYPE = 'XYZ'
 where a.receive_date in (select max(receive_date)
                                             from a a1
                                             inner join b b1 on b1.aid = a1.aid   
                                             where b1.itemid = b.itemid
                                             );

How can we rewrite this SQL query without IN clause [also without rownumber concept in use], as ORACLE is having restriction for IN clause. Getting java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 for above expression.

Are you sure this is the query produced the error. AFAIK, that restriction is applied only when values are passed in `IN` clause like `a.receive_date in ('2017-01-01','2017-02-01',..)` — Pரதீப், Jul 12 '17 at 04:24
And you can simply change `IN` to `=` and get the same result, instead of rewrite query using `INNER JOIN` — Pham X. Bach, Jul 12 '17 at 04:26
@Prdp Maybe the error is being caused by having an intermediate query inside an `IN` expression which has too many records. Just a guess. — Tim Biegeleisen, Jul 12 '17 at 04:37
@TimBiegeleisen - It isn't a problem in Sql Server not sure about Oracle though.. — Pரதீப், Jul 12 '17 at 04:40
@prdp Yes it is throwing the exception, but not for the limit of 1k, even for 2k-3k data no error is coming but when it crossing data more than 10, then above exception is coming. SQL server is not having any IN clause restriction. — Technogix, Jul 12 '17 at 04:42
There are two form of `IN` clause - value based and sub-query based. The restriction only applies to they 1st one. 2nd one is silently rewritten onto a join by the optimizer. I do not believe, that your query really returns that error. You might get an error when the sub-query returned more that one column - but it is a semantic error. Your sub-query returns only a single value so `IN` can be even replaced by pure `=`. I think you "anonymised" the query before posting and the real problem is somewhere else. ps: you can avoid self-joins by using analytic functions. — ibre5041, Jul 12 '17 at 05:31
If one of your tables has > 1000 partitions then the ORA-01795 may be related to a bug referred to here:- https://stackoverflow.com/a/45050530/430567 (also mentions NO_EXPAND_TABLE hint which you could try to verify if this is the issue), — Lord Peter, Jul 12 '17 at 07:18

Tim Biegeleisen · Accepted Answer · 2017-07-12T04:40:40.577

It isn't clear to me why you are getting ORA-01795. Your subquery only selects a max value, which should be a single value. In addition, the 1000 value limit only applies to a list of literals, not a subquery. In any case, you could rephrase this query using a join instead of WHERE IN:

SELECT 
    b.MESSAGE,
    b.ITEMID
FROM a
INNER JOIN b
    ON b.aid = a.aid AND b.ITEMTYPE = 'XYZ'
INNER JOIN
(
    SELECT
        b1.itemid,
        MAX(receive_date) AS max_receive_date
    FROM a a1
    INNER JOIN b b1
        ON b1.aid = a1.aid   
    GROUP BY b1.itemid
) t
    ON b.itemid = t.itemid
WHERE a.receive_date = t.max_receive_date

score 2 · Answer 2 · answered Jul 12 '17 at 04:49

EXISTS and IN tend to be interchangeable, and EXISTS performs better in some engines (not sure about Oracle) due to the fact that it returns true on the first match, rather than generating a subset and checking against that. I'm not familiar with Oracle, but I imagine you could use the following to circumvent your 1000 row limit on IN:

SELECT 
    b.MESSAGE, b.ITEMID
from a
inner join b on b.aid = a.aid AND b.ITEMTYPE = 'XYZ'
where exists (
    SELECT 1
    from a a1
    inner join b b1 on b1.aid = a1.aid   
    where b1.itemid = b.itemid
    having MAX(a1.receive_date) = a.receive_date
)

How to rewrite SQL query without IN clause

2 Answers2