SQL Query Comparing Two varray()

user_4385709 0
Viewed 58

I have a table of employees. One of the columns is a varray() that contains multiple room #'s for their office. I'm looking for a simple query that will compare each employee to see if they share an office.

SELECT  E1.Name, E2.Name
FROM    Employee E1
JOIN    Employee E2
ON      E1.Room = E2.Room;

Something like this doesn't work because the Room column is a varray. I just need one value in the first varray to match with another in the second. Is there an easy way of doing this?

oracle sql
1 Answers

Assuming you refer to Oracle, the query of your choice could be either

select
    E1.name as employee_1, E2.name as employee_2,
    R1.column_value as the_matching_room
from employee E1
    cross join table(E1.rooms) R1
    join employee E2
        on E2.emp_id > E1.emp_id
    join table(E2.rooms) R2
        on R2.column_value = R1.column_value
;

or (somewhat more effective)

with rooms_unnested$ as (
    select E.emp_id, E.name, R.column_value as room
    from employee E
        cross join table(E.rooms) R
)
select
    E1.name as employee_1, E2.name as employee_2,
    E1.room as the_matching_room
from rooms_unnested$ E1
    join rooms_unnested$ E2
        on E2.emp_id > E1.emp_id
        and E2.room = E1.room
;

This one has the potential problem of doing the cartesian between employee tables first, unnesting the collections later:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows    | Bytes      | Cost   | Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          | 1334324 | 5142484696 | 447202 | 00:00:18 |
|   1 |   NESTED LOOPS                        |          | 1334324 | 5142484696 | 447202 | 00:00:18 |
|   2 |    NESTED LOOPS                       |          |   16336 |   62926272 |     63 | 00:00:01 |
|   3 |     NESTED LOOPS                      |          |       2 |       7700 |      7 | 00:00:01 |
|   4 |      TABLE ACCESS FULL                | EMPLOYEE |       2 |       3850 |      3 | 00:00:01 |
| * 5 |      TABLE ACCESS FULL                | EMPLOYEE |       1 |       1925 |      2 | 00:00:01 |
|   6 |     COLLECTION ITERATOR PICKLER FETCH |          |    8168 |      16336 |     28 | 00:00:01 |
| * 7 |    COLLECTION ITERATOR PICKLER FETCH  |          |      82 |        164 |     27 | 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - filter("E2"."EMP_ID">"E1"."EMP_ID")
* 7 - filter(VALUE(KOKBF$)=VALUE(KOKBF$))

With the assumption that your "rooms" varrays may contain duplicates, there's one more tweak to do - making each employee's rooms distinct, which leads us to the (hopefully) final query...

with rooms_unnested$ as (
    select distinct
        E.emp_id, E.name, R.column_value as room
    from employee E
        cross join table(E.rooms) R
)
select
    E1.name as employee_1, E2.name as employee_2,
    E1.room as the_matching_room
from rooms_unnested$ E1
    join rooms_unnested$ E2
        on E2.emp_id > E1.emp_id
        and E2.room = E1.room
;

... which also happens to resolve the "issue" with cartesians by unnesting the "rooms" varray first (and only once!) and equi-hash-joining afterwards:

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                        | Rows  | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                             |     1 |    120 |   65 | 00:00:01 |
|   1 |   TEMP TABLE TRANSFORMATION                |                             |       |        |      |          |
|   2 |    LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6699_11FF28DD |       |        |      |          |
|   3 |     HASH UNIQUE                            |                             |     3 |     36 |   61 | 00:00:01 |
|   4 |      NESTED LOOPS                          |                             | 16336 | 196032 |   59 | 00:00:01 |
|   5 |       TABLE ACCESS FULL                    | EMPLOYEE                    |     2 |     20 |    3 | 00:00:01 |
|   6 |       COLLECTION ITERATOR PICKLER FETCH    |                             |  8168 |  16336 |   28 | 00:00:01 |
| * 7 |    HASH JOIN                               |                             |     1 |    120 |    4 | 00:00:01 |
|   8 |     VIEW                                   |                             |     3 |    180 |    2 | 00:00:01 |
|   9 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6699_11FF28DD |     3 |     36 |    2 | 00:00:01 |
|  10 |     VIEW                                   |                             |     3 |    180 |    2 | 00:00:01 |
|  11 |      TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6699_11FF28DD |     3 |     36 |    2 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 7 - access("E2"."ROOM"="E1"."ROOM")
* 7 - filter("E2"."EMP_ID">"E1"."EMP_ID")
user_37061810
Related