SQL - duplicate search in two columns

Question

I have a table with four columns, where col1they col2contain similar values ​​(INT). Now I want to know if there are duplicates in col1and / 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.

Answer 1

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

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

Add HAVING COUNT(*) > 1if you want to see only duplicate values.

Answer 2

, (.. 333 "", 333 ), . .

, :

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;

Answer 3

Attach the table to yourself to see if there are duplicates.

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