How to compare rows as lists in SQL?

I have a many to many table which has 3 primary keys shown below :

table1 : key_1, key_2, key_3

I want to compare rows as lists,

For Example :

table1 would be :

      key_1  key_2  key_3

row1:  10  |  100  | 150
row2:  10  |  101  | 150
row3:  10  |  103  | 151

row4:  11  |  100  | 150
row5:  11  |  101  | 150
row6:  11  |  103  | 151

So what I’d like to achive is to compare my table filtered by key_1 and find duplicate list of rows.

So in this scenerio,

SELECT * FROM table1 where key_1 = 10;

returns 3 rows (row1, row2, row3) and

SELECT * FROM table1 where key_1 = 11;

also returns 3 rows (row4, row5, row6)

And as you see above, first result of 3 rows has same key_2 & key_3 values with the second result of 3 rows.

So how can I query this, get rows as lists and compare them?

I know that this question looks something stupid but please, I’d very glad if you help me. Thanks in advance 🙂

Answer

You can search for unmatched rows of a full outer join.

For example the following query finds any difference between group 10 and 11:

select *
from table1 a
full join table1 b on a.key2 = b.key2 and a.key3 = b.key3
where a.key2 is null or b.key2 is null
  and a.key1 = 10 and b.key1 = 11

If the query returns no rows, then the groups are identical.

Advertisement

Answer