Can we fire a sql query with multiple where IN clause , if yes how does it work?

Question

A query of the format select * from student where id in(1,2,3,4) works like

select * from student where id=1;
select * from student where id=2;
select * from student where id=3;
select * from student where id=4;

This is what I know to the best of my knowledge.

Now I want to fire a query with multiple WHERE IN CLAUSE

SELECT    Count(*) NO_OF_PO, QUANTITY FROM    INTER_DEFECT idp
WHERE    DEFECT_ID IN(1,2,3) 
AND    PO_NUMBER IN (4,5,6)  
AND    SUPP_ID IN (7,8,9)  
AND    LINE_NO IN (10,11,12)    
AND    RECEIPT_NO IN(13,14,15)

I want this to fire like this in reality

SELECT    Count(*) NO_OF_PO, QUANTITY FROM    INTER_DEFECT idp
WHERE    DEFECT_ID =1
AND    PO_NUMBER =4
AND    SUPP_ID = 7
AND    LINE_NO = 10
AND    RECEIPT_NO=13

 SELECT    Count(*) NO_OF_PO, QUANTITY FROM    INTER_DEFECT idp
    WHERE    DEFECT_ID =2
    AND    PO_NUMBER =5
    AND    SUPP_ID = 8
    AND    LINE_NO = 11
    AND    RECEIPT_NO=14

etc etc.

So you see the Parameter order should not change like

DEFECT_ID =1 should fire with PO_NUMBER =4 for example

DEFECT_ID =1 AND PO_NUMBER =4 ....

And DEFECT_ID =2 should fire with PO_NUMBER =5 for example

DEFECT_ID =2 AND PO_NUMBER =5 ....

Is this possible in Oracle or any other Database ?

Answer 1

Oracle supports in clauses with multiple expressions. That is, you can do:

SELECT Count(*) NO_OF_PO, QUANTITY
FROM INTER_DEFECT idp
WHERE (DEFECT_ID, PO_NUMBER, SUPP_ID, LINE_NO, RECEIPT_NO) IN
       ( (1, 4, 7, 10, 13), (2, 5, 8, 11, 14), (3, 6, 9, 12, 15) );

Not all databases support this construct. Normally, you would just use AND and OR:

WHERE (DEFECT_ID = 1 and PO_NUMBER = 4 . . . ) OR
      (DEFECT_ID = 2 and PO_NUMBER = 5 . . . ) OR
      (DEFECT_ID = 3 and PO_NUMBER = 6 . . . )

Or use join with a CTE or subquery that has the right values.