Nested tables can be combined and compared like sets: united, intersected, subtracted, and tested for membership. The multiset operators and conditions do this in one expression, in PL/SQL and in SQL, instead of nested loops over the elements.
Code for This Guide
The main examples are in the examples/collections folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
nt1 multiset {union | intersect | except} [all | distinct] nt2
set(nt)
expr member of nt
nt is [not] a set
nt is [not] empty
nt1 submultiset of nt2ALL, the default, keeps duplicates; DISTINCT removes them. The operands must be nested tables of the same type.
Combine and Test Two Lists
The example uses a schema-level nested table type, created first:
Setup:
create or replace type t_code_list as table of varchar2(3);
Example:
declare
v_a t_code_list := t_code_list('DXB', 'LHR', 'SIN', 'LHR');
v_b t_code_list := t_code_list('LHR', 'SYD');
procedure show (p_label varchar2, p_list t_code_list) is
v_text varchar2(100);
begin
for i in 1 .. p_list.count loop v_text := v_text || p_list(i) || ' '; end loop;
dbms_output.put_line(rpad(p_label, 20) || v_text);
end;
begin
show('union', v_a multiset union v_b);
show('union distinct', v_a multiset union distinct v_b);
show('intersect', v_a multiset intersect v_b);
show('except', v_a multiset except v_b);
show('set', set(v_a));
dbms_output.put_line('SIN member of a? '
|| case when 'SIN' member of v_a then 'yes' else 'no' end);
dbms_output.put_line('a is a set? '
|| case when v_a is a set then 'yes' else 'no' end);
dbms_output.put_line('b submultiset of a? '
|| case when v_b submultiset of v_a then 'yes' else 'no' end);
end;
/Output:
union DXB LHR SIN LHR LHR SYD union distinct DXB LHR SIN SYD intersect LHR except DXB SIN LHR set DXB LHR SIN SIN member of a? yes a is a set? no b submultiset of a? no PL/SQL procedure successfully completed.
| Expression | Result |
|---|---|
| MULTISET UNION | All elements of both, duplicates kept |
| MULTISET UNION DISTINCT | All elements of both, once each |
| MULTISET INTERSECT | Elements in both |
| MULTISET EXCEPT | Elements of the first not in the second; one LHR remains, since only one was removed |
| SET | The first list without duplicates |
MEMBER OF finds SIN in the first list, IS A SET is false because LHR appears twice, and the second list is not a submultiset of the first, since SYD is missing. The example drops the type afterward.
Things to Know
- The operators work in SQL on nested table columns and values too, for example WHERE 'DXB' MEMBER OF airports_list.
- Elements are compared by value; object elements need a MAP or ORDER method.
- CARDINALITY counts the elements of the result.
Related Guides
Conclusion
Multiset operators unite, intersect, and subtract nested tables, with ALL or DISTINCT, and the conditions MEMBER OF, IS A SET, IS EMPTY, and SUBMULTISET OF test them. Use them for set logic on lists in PL/SQL and SQL without writing loops.
