Minus all doesn't work although minus works

July 1, 2022

SELECT jt.*
  FROM JSON_TABLE (
           TO_CLOB ('[{"A":1,"B":11},{"A":1,"B":11}]'),
           '$[*]'
           COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
       AS jt
MINUS --all
SELECT jt.*
  FROM JSON_TABLE (
           TO_CLOB ('[{"A":1,"B":11},{"A":12,"B":111}]'),
           '$[*]'
           COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
       AS jt

the first query return this table

A	B
1	11
1	111

the second query return this table

a	b
1	11
12	111

with minus or minus all I should become this table

a	b
1	11

that’s what what I’ve got with minus but minus doesn’t work
why?

code

>Solution :

You can achieve it with the help of row_number() window function.

Since in below query every A and B combination has unique row numbers only matching number of rows will be removed.

Query:

 select row_number()over(partition by A,B order by A,B)rn,A,B from (SELECT jt.*
   FROM JSON_TABLE (
            TO_CLOB ('[{"A":1,"B":11},{"A":1,"B":11}]'),
            '$[*]'
            COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
        AS jt)
 MINUS --all
 select row_number()over(partition by A,B order by A,B)rn,A,B from (SELECT jt.*
   FROM JSON_TABLE (
            TO_CLOB ('[{"A":1,"B":11},{"A":12,"B":111}]'),
            '$[*]'
            COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
        AS jt)

Output: