sql - find duplicates in two columns

Question

Hello,

I have a table with four columns, where col1 & col2 contain similar values (INT). I now want to know, if there are duplicates in col1 and/or col2. i.e.,

col1 | col2
-----+-----
111  | 222
333  | 444
111  | 333
555  | 111

→ Duplicates: 111 (3x) and 333 (2x).

I use sqlite, but I think this is a basic sql question.

Thanks,
mspoerr

Answer 1

To get a count of each element use UNION ALL in a subquery, then GROUP BY on the result of that:

SELECT col, COUNT(*)
FROM
(
    SELECT col1 AS col FROM Table1
    UNION ALL
    SELECT col2 FROM Table1
) T1
GROUP BY col

Add HAVING COUNT(*) > 1 if you only wish to see the duplicated values.

Answer 2

Join the table to itself to see if there are any duplicates.

select t1.col1, t2.col2
from table t1 inner join table t2 
  on t1.col1 = t2.col2

Answer 3

If you have two columns where the values are compatible (that is, 333 in one column represents the same "thing" as 333 in the other column) then this is called repeating groups. This is a violation of First Normal Form.

If the values were in a single column, finding duplicates would be easy:

CREATE TABLE pairs (
  pair_id INT,
  value   INT,
  PRIMARY KEY (pair_id, value)
);

INSERT INTO pairs VALUES
(1, 111), (1, 222),
(2, 333), (2, 444),
(3, 111), (3, 333),
(4, 555), (4, 111);

SELECT value, COUNT(*)
FROM pairs
GROUP BY value
HAVING COUNT(*) > 1;