A nested table can contain the same value more than once. The SET function returns the collection with its duplicates removed, the collection equivalent of SELECT DISTINCT. It is most useful after combining collections, where duplicates appear naturally.
Code for This Guide
The main example is in the examples/misc-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
set(nested_table)
The argument is a nested table, and the result is a nested table of the same type. The examples use this type:
Setup:
create or replace type t_codes as table of varchar2(10);
Remove Duplicates
Example:
select cardinality(t_codes('DXB', 'LHR', 'DXB', 'SIN')) as items,
cardinality(set(t_codes('DXB', 'LHR', 'DXB', 'SIN'))) as distinct_items,
set(t_codes('DXB', 'LHR', 'DXB', 'SIN')) as set_result
from dual;Output:
ITEMS DISTINCT_ITEMS SET_RESULT
________ _________________ __________________
4 3 [DXB, LHR, SIN]The collection DXB, LHR, DXB, SIN has four elements; SET returns DXB, LHR, SIN, three distinct elements.
After Combining Collections
The multiset operator MULTISET UNION combines two nested tables and keeps duplicates, like UNION ALL. Applying SET to the result removes them, which gives the same result as MULTISET UNION DISTINCT.
Example:
-- SET removes the duplicates that MULTISET UNION keeps
select t_codes('DXB', 'LHR') multiset union t_codes('LHR', 'SIN') as union_all_like,
set(t_codes('DXB', 'LHR') multiset union t_codes('LHR', 'SIN')) as union_distinct,
t_codes('DXB', 'LHR') multiset union distinct t_codes('LHR', 'SIN') as union_distinct_op
from dual;Output:
UNION_ALL_LIKE UNION_DISTINCT UNION_DISTINCT_OP _______________________ __________________ ____________________ [DXB, LHR, LHR, SIN] [DXB, LHR, SIN] [DXB, LHR, SIN]
Things to Know
- The element type must be comparable: SET works on scalar types and on object types that have a MAP or ORDER method, not on LOBs.
- The condition x IS A SET is true when a nested table has no duplicates.
- SET does not sort; the order of elements in a nested table is not guaranteed, so sort with TABLE() and ORDER BY when order matters.
Related Guides
Conclusion
SET removes duplicate elements from a nested table and returns a collection of the same type. Use it after MULTISET UNION and other combinations, and test for duplicates with the IS A SET condition.
