Skip to main content
← PL/SQL Topics

Collections

Explain MULTISET UNION, INTERSECT and EXCEPT on nested tables

MULTISET Operators

ExplanationOracle Fusion / EBS · Technical · PL/SQLHigh confidenceGenerated

MULTISET operators do set arithmetic between two nested-table variables, returning a new nested table.

How it works

For two nested tables of the same element type: a MULTISET UNION b (add, with ALL keeping duplicates or DISTINCT removing them), a MULTISET INTERSECT b (common elements), a MULTISET EXCEPT b (in a but not b). Related predicates: x MEMBER OF a, a IS A SET (no duplicates), a IS EMPTY, a SUBMULTISET OF b, and CARDINALITY(a) for the count. These work only on nested tables, not associative arrays or VARRAYs.

How it works
DECLARE
  TYPE num_nt IS TABLE OF NUMBER;
  a num_nt := num_nt(1, 2, 3, 4);
  b num_nt := num_nt(3, 4, 5);
  r num_nt;
BEGIN
  r := a MULTISET INTERSECT b;             -- (3, 4)
  DBMS_OUTPUT.PUT_LINE('common count: ' || CARDINALITY(r));
  r := a MULTISET EXCEPT b;                -- (1, 2)
  DBMS_OUTPUT.PUT_LINE('only in a: ' || r.COUNT);
END;
/

Related questions

GroundingGenerated

Model-generated, grounded against the curated knowledge layer. Check specifics against your instance.

Have a follow-up, or a different question?

Continue in Ask Oracle AI