Как найти вхождения из нескольких столбцов одной таблицы в одном столбце другой?

Question

Как найти все вхождения из нескольких столбцов одной таблицы в одном столбце другой?

Пусть, например, заданы таблицы table1 и table2:

CREATE TABLE table1 (
  colmA TEXT,
  colmB TEXT
);

INSERT INTO table1 (colmA, colmB)
VALUES
  ('aa', 'nn'),
  ('bb', 'yy'),
  ('cc', 'll'),
  ('dd', 'aa');


CREATE TABLE table2 (
  colmA TEXT
);

INSERT INTO table2 (colmA)
VALUES
  ('xx'),
  ('zz'),
  ('aa'),
  ('cc'),
  ('yy');

Как из этих таблиц получить такой результат:

┌──────────┐
│ selected │
├──────────┤
│    aa    │
│    cc    │
│    yy    │
└──────────┘

Update

Из ответов выбрал вариант:

SELECT stack.selected
FROM (
  SELECT colmA AS selected FROM table1
  UNION
  SELECT colmB FROM table1
  ) AS stack
INNER JOIN table2 AS reference
ON stack.selected = reference.colmA;

Скорость обработки: 27 минут обрабатывает 5 млн записей. Как ускорить? Возможно подход сменить? Как сравнить два многомиллионных списка быстро и найти общие записи?

Answer 1

Ну вроде обычный INNER JOIN должен справиться:

SELECT t1.colmA FROM table1 t1
          INNER JOIN table2 t2 ON t2.colmA=t1.colmA

Answer 2

С помощью UNION объединяем нужные столбцы в один. При этом по умолчанию будут отобраны уникальные значение. Дальше через INNER JOIN оставляем только общие значения.

Например:

SELECT stack.selected
FROM (
  SELECT colmA AS selected FROM table1
  UNION
  SELECT colmB FROM table1
  ) AS stack
INNER JOIN table2 AS reference
ON stack.selected = reference.colmA;

Чтобы при объединении сохранить дубликаты, используется UNION ALL. Тогда, чтобы получить уникальные значения, нам понадобится SELECT DISTINCT или GROUP BY. В последнем случае мы получим дополнительные возможности. Например, можем посчитать количество вхождений общих элементов.

SELECT
  stack.selected,
  count(*) as 'count'
FROM (
  SELECT colmA AS selected FROM table1
  UNION ALL
  SELECT colmB FROM table1
  ) AS stack
INNER JOIN table2 AS reference
ON stack.selected = reference.colmA
GROUP BY stack.selected
ORDER BY 2 DESC, 1 ASC;

Важный нюанс: если мы не уверены в уникальности строк референтной таблицы table2, то перед присоединением нужно очистить соответствующее поле от дубликатов:

SELECT stack.selected
FROM (
  SELECT colmA AS selected FROM table1
  UNION
  SELECT colmB FROM table1
  ) AS stack
INNER JOIN (
  SELECT DISTINCT colmA AS value
  FROM table2
  ) AS reference
ON stack.selected = reference.value;

Answer 3

https://sqlfiddle.com/sqlite/online-compiler?id=42f56916-ac54-46b0-ad6e-97c1d958e848

select distinct t2.colmA as selected
from table2 t2 inner join table1 t1
on t2.colmA = t1.colmA or t2.colmA = t1.colmB

Если хочется посчитать количество совпадений, то надо понять, как именно их хочется посчитать (для каждой из колонок, для строк или для ячеек) и выбрать соответствующую формулу:

https://sqlfiddle.com/mysql/online-compiler?id=e162d3d3-9a07-418d-ad39-f0eb2657c1c7

select
  t2.colmA as selected,
  sum(t2.colmA = t1.colmA) as match_a,
  sum(t2.colmA = t1.colmB) as match_b,
  sum(t2.colmA = t1.colmA or t2.colmA = t1.colmB) as match_rows,
  sum(t2.colmA = t1.colmA) + sum(t2.colmA = t1.colmB) as match_cells
from table2 t2 inner join table1 t1
on t2.colmA = t1.colmA or t2.colmA = t1.colmB
group by t2.colmA

Для таких исходных данных в table1:

colmA	colmB
aa	nn
bb	yy
cc	ll
dd	aa
qq	qq

И значений в table2:

xx, zz, aa, cc, yy, qq

Получится результат:

selected	match_a	match_b	match_rows	match_cells
aa	1	1	2	2
cc	1	0	1	1
yy	0	1	1	1
qq	1	1	1	2

Answer 4

Если проиндексировать colmA и colmB (отдельно) в первой таблице и colmA во второй, то UNION должен дать более шустрый результат, чем использование в условии OR. Не все оптимизаторы OR разбирают сами на UNION, а оно убивает индексы.

SELECT DISTINCT * FROM (
  SELECT t2.colmA selected
  FROM table2 t2 
  INNER JOIN table1 t1 ON t2.colmA = t1.colmA
  
  UNION ALL
  
  SELECT t2.colmA
  FROM table2 t2 
  INNER JOIN table1 t1 ON t2.colmA = t1.colmB
)

Если DISTINCT написать внутри каждого запроса и использовать UNION, то мы трижды потратим время на отбор уникальных записей. Поэтому я использовал UNION ALL и один DISTINCT снаружи.

БЛОГ НА HUSL

Как найти вхождения из нескольких столбцов одной таблицы в одном столбце другой?

Ответы (4 шт):