SQL: How To Select Data With Same Values On Columns

Let say you have the following data in you database table t1 and you want to select the IDs for the row record that have the same value on COL1 and COL2.

ID   COL1   COL2   COL3
======================
1    100    200    300
2    200    300    400
3    100    200    500
4    300    300    300
5    200    300    400

And the expected result is ID = 1,2,3,5. The SQL to achive the result is:

select 
    left.ID
from
    t1 left inner join t1 right
on
    left.COL1 = right.COL1 and
    left.COL2 = right.COL2 and
    left.ID <> right.ID

You can also use “group by” then “count(*)” method but it is a bit more complicated and slow.