Suppose you have a set of data in Oracle, how can you find any other matching sets of data? This is different then most queries in SQL which just have “standard” predicates. For example most queries answer the question “*find me all cars that are red*“ (`where color = 'red'`

) whereas set comparison is answering the question “*find me all cars that are the same as a given set of cars*“.

A good example of this problem is if you need to find orders that are **exactly** the same as a given order. An order can have multiple products in it. It’s not as easy as comparing one row to another, rather sets of rows to other sets of rows.

## Set Theory

Before continuing it’s important to go over some basic set theory. One way to think of sets are as arrays of data. When comparing for equivalence of sets we need to ask two questions:

- Is everything in
`A`

in`B`

? (i.e.`A - B = 0`

) - Is everything in
`B`

in`A`

? (i.e.`B - A = 0`

)

At first glance, the second point may seem unnecessary but it isn’t as `A`

may be a subset of `B`

.

Take for example the following arrays:

`A = 1,2,3`

`B = 1,2,3,4`

In the above case, everything in `A`

is in `B`

, however everything in `B`

(i.e. `4`

) is not in `A`

.

## Example

Suppose we have a table (`tab_a`

) and we want to find any other tables with the exact same column definition.

1 | -- Base table to be compared against |

The above script will create three tables. If we start with `tab_a`

only `tab_c`

is the exact same. Using the set theory logic above, and the `minus`

set operator clause in Oracle you can find matching tables using the following query:

1 | select at_match.table_name |