sql nosql

SQL/NoSql/Oracle >> PL/SQL >> union,intersection of collection

union,intersection of collection

By ukmodak | March 31st 2024 10:32:28 AM | viewed 304 times

MULTISET UNION {ALL | DISTINCT} Operator

The MULTISET UNION operator joins the two collections together, doing the equivalent of a UNION ALL between the two sets. The MULTISET UNION and MULTISET UNION ALL operators are functionally equivalent.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6);
  l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET UNION l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/

The DISTINCT keyword can be added to any of the multiset operations to removes the duplicates. Adding it to the MULTISET UNION operator makes it the equivalent of a UNION between the two sets.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6);
  l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET UNION DISTINCT l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/

MULTISET EXCEPT {DISTINCT} Operator

The MULTISET EXCEPT operator returns the elements of the first set that are not present in the second set, doing the equivalent of the MINUS set operator. The MULTISET EXCEPT DISTINCT operator will remove any duplicates.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10);
  l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET EXCEPT l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/

MULTISET INTERSECT {DISTINCT} Operator

The MULTISET INTERSECT operator returns the elements that are present in both sets, doing the equivalent of the INTERSECT set operator. The MULTISET INTERSECT DISTINCT operator will remove any duplicates.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10);
  l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET INTERSECT l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/

MULTISET Conditions IS {NOT} A SET Condition

The IS {NOT} A SET condition is used to test if a collection is populated by unique elements, or not. If the collection is not initialized the function will return NULL. An initialised and empty collection will return true.


SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_null_tab      t_tab := NULL;
  l_empty_tab     t_tab := t_tab();
  l_set_tab       t_tab := t_tab(1,2,3,4);
  l_not_set_tab   t_tab := t_tab(1,2,3,4,4,4);

  FUNCTION display (p_in BOOLEAN) RETURN VARCHAR2 AS
  BEGIN
    IF p_in IS NULL THEN
      RETURN 'NULL';
    ELSIF p_in THEN
      RETURN 'TRUE';
    ELSE
      RETURN 'FALSE';
    END IF;
  END;
BEGIN
  DBMS_OUTPUT.put_line('l_null_tab IS A SET          = ' || display(l_null_tab IS A SET));
  DBMS_OUTPUT.put_line('l_null_tab IS NOT A SET      = ' || display(l_null_tab IS NOT A SET));
  DBMS_OUTPUT.put_line('l_empty_tab IS A SET         = ' || display(l_empty_tab IS A SET));
  DBMS_OUTPUT.put_line('l_empty_tab IS NOT A SET     = ' || display(l_empty_tab IS NOT A SET));
  DBMS_OUTPUT.put_line('l_set_tab IS A SET           = ' || display(l_set_tab IS A SET));
  DBMS_OUTPUT.put_line('l_set_tab IS NOT A SET       = ' || display(l_set_tab IS NOT A SET));
  DBMS_OUTPUT.put_line('l_not_set_tab IS A SET       = ' || display(l_not_set_tab IS A SET));
  DBMS_OUTPUT.put_line('l_not_set_tab IS NOT A SET   = ' || display(l_not_set_tab IS NOT A SET));
END;
/

IS {NOT} EMPTY Condition

The IS {NOT} EMPTY condition is used to test if a collection is empty, or not. If the collection is not initialized the function will return NULL.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_null_tab      t_tab := NULL;
  l_empty_tab     t_tab := t_tab();
  l_not_empty_tab t_tab := t_tab(1,2,3,4,4,4);

  FUNCTION display (p_in BOOLEAN) RETURN VARCHAR2 AS
  BEGIN
    IF p_in IS NULL THEN
      RETURN 'NULL';
    ELSIF p_in THEN
      RETURN 'TRUE';
    ELSE
      RETURN 'FALSE';
    END IF;
  END;
BEGIN
  DBMS_OUTPUT.put_line('l_null_tab IS EMPTY          = ' || display(l_null_tab IS EMPTY));
  DBMS_OUTPUT.put_line('l_null_tab IS NOT EMPTY      = ' || display(l_null_tab IS NOT EMPTY));
  DBMS_OUTPUT.put_line('l_empty_tab IS EMPTY         = ' || display(l_empty_tab IS EMPTY));
  DBMS_OUTPUT.put_line('l_empty_tab IS NOT EMPTY     = ' || display(l_empty_tab IS NOT EMPTY));
  DBMS_OUTPUT.put_line('l_not_empty_tab IS EMPTY     = ' || display(l_not_empty_tab IS EMPTY));
  DBMS_OUTPUT.put_line('l_not_empty_tab IS NOT EMPTY = ' || display(l_not_empty_tab IS NOT EMPTY));
END;
/

MEMBER Condition

The MEMBER condition allows you to test if an element is member of a collection.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5);
BEGIN
  DBMS_OUTPUT.put('Is 3 MEMBER OF l_tab1? ');
  IF 3 MEMBER OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/

SUBMULTISET Condition

The SUBMULTISET condition returns true if the first collection is a subset of the second.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5);
  l_tab2 t_tab := t_tab(1,2,3);
  l_tab3 t_tab := t_tab(1,2,3,7);
BEGIN
  DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
  IF l_tab2 SUBMULTISET OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;

  DBMS_OUTPUT.put('Is l_tab3 SUBMULTISET OF l_tab1? ');
  IF l_tab3 SUBMULTISET OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/

An initialised, but empty subset will always return true.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5);
  l_tab2 t_tab := t_tab();
BEGIN
  DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
  IF l_tab2 SUBMULTISET OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/

....................


SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab();
  l_tab2 t_tab := t_tab();
BEGIN
  DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
  IF l_tab2 SUBMULTISET OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/

MULTISET Functions CARDINALITY Function

The CARDINALITY function returns the number of elements in the collection, similar to the COUNT method, but it is available from SQL.

CREATE OR REPLACE TYPE t_number_tab AS TABLE OF NUMBER(10);
/

SELECT CARDINALITY(tab1)
FROM   (SELECT t_number_tab (1, 2, 3, 4) AS tab1
        FROM dual);

---output: 4

SELECT tab1
FROM   (SELECT t_number_tab(1, 2, 3, 4) AS tab1
        FROM dual)
WHERE  CARDINALITY(tab1) = 4;

--- output: T_NUMBER_TAB(1, 2, 3, 4)


--or

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5);
BEGIN
  DBMS_OUTPUT.put_line('COUNT       = ' || l_tab1.COUNT);
  DBMS_OUTPUT.put_line('CARDINALITY = ' || CARDINALITY(l_tab1));
END;
/

output:
COUNT       = 5
CARDINALITY = 5

POWERMULTISET Function

The POWERMULTISET function accepts a nested table and returns a "nested table of nested tables" containing all the possible subsets from the original nested table.

CREATE OR REPLACE TYPE t_number_tab AS TABLE OF NUMBER(10);
/
SELECT *
FROM   TABLE(POWERMULTISET(t_number_tab (1, 2, 3, 4)));

output:

COLUMN_VALUE
----------------------------------------
T_NUMBER_TAB(1)
T_NUMBER_TAB(2)
T_NUMBER_TAB(1, 2)
T_NUMBER_TAB(3)
T_NUMBER_TAB(1, 3)
T_NUMBER_TAB(2, 3)
T_NUMBER_TAB(1, 2, 3)
T_NUMBER_TAB(4)
T_NUMBER_TAB(1, 4)
T_NUMBER_TAB(2, 4)
T_NUMBER_TAB(1, 2, 4)
T_NUMBER_TAB(3, 4)
T_NUMBER_TAB(1, 3, 4)
T_NUMBER_TAB(2, 3, 4)
T_NUMBER_TAB(1, 2, 3, 4)

POWERMULTISET_BY_CARDINALITY Function

The POWERMULTISET_BY_CARDINALITY function is similar to the POWERMULTISET function, but it allows us to limit the output to just those subsets that have the specified cardinality. In the following example we return only those subsets that have a cardinality of 2.

SELECT *
FROM   TABLE(POWERMULTISET_BY_CARDINALITY(t_number_tab (1, 2, 3, 4), 2));

output:
T_NUMBER_TAB(1, 2)
T_NUMBER_TAB(1, 3)
T_NUMBER_TAB(1, 4)
T_NUMBER_TAB(2, 3)
T_NUMBER_TAB(2, 4)
T_NUMBER_TAB(3, 4)

SET Function

The SET function returns a collection containing the distinct values from a collection.

CREATE OR REPLACE TYPE t_number_tab AS TABLE OF NUMBER(10);
/

SET LINESIZE 100
COLUMN basic_out FORMAT A35
COLUMN set_out FORMAT A35

SELECT tab1 AS basic_out,
       SET(tab1) AS set_out,
       CARDINALITY(tab1) AS card_out,
       CARDINALITY(SET(tab1)) AS card_set
FROM   (SELECT t_number_tab (1, 2, 3, 4, 4, 4) AS tab1
        FROM dual);

output:

BASIC_OUT                           SET_OUT                               CARD_OUT   CARD_SET
----------------------------------- ----------------------------------- ---------- ----------
T_NUMBER_TAB(1, 2, 3, 4, 4, 4)      T_NUMBER_TAB(1, 2, 3, 4)                     6          4

Multidimensional Collections

In addition to regular data types, collections can be based on record types, allowing the creation of two-dimensional collections.

Latest Tutorial