Альтернатива Intersect в MySQL

Ответ 1

Microsoft SQL Server INTERSECT "возвращает любые различные значения, возвращаемые как запросом слева и справа от операнда INTERSECT" Это отличается от стандартного запроса INNER JOIN или WHERE EXISTS.

SQL Server

CREATE TABLE table_a (
    id INT PRIMARY KEY,
    value VARCHAR(255)
);

CREATE TABLE table_b (
    id INT PRIMARY KEY,
    value VARCHAR(255)
);

INSERT INTO table_a VALUES (1, 'A'), (2, 'B'), (3, 'B');
INSERT INTO table_b VALUES (1, 'B');

SELECT value FROM table_a
INTERSECT
SELECT value FROM table_b

value
-----
B

(1 rows affected)

MySQL

CREATE TABLE `table_a` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `value` varchar(255),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `table_b` LIKE `table_a`;

INSERT INTO table_a VALUES (1, 'A'), (2, 'B'), (3, 'B');
INSERT INTO table_b VALUES (1, 'B');

SELECT value FROM table_a
INNER JOIN table_b
USING (value);

+-------+
| value |
+-------+
| B     |
| B     |
+-------+
2 rows in set (0.00 sec)

SELECT value FROM table_a
WHERE (value) IN
(SELECT value FROM table_b);

+-------+
| value |
+-------+
| B     |
| B     |
+-------+

В этом конкретном вопросе задействован столбец id, поэтому повторяющиеся значения не будут возвращены, но для полноты здесь используется альтернатива MySQL с использованием INNER JOIN и DISTINCT:

SELECT DISTINCT value FROM table_a
INNER JOIN table_b
USING (value);

+-------+
| value |
+-------+
| B     |
+-------+

И еще один пример с использованием WHERE ... IN и DISTINCT:

SELECT DISTINCT value FROM table_a
WHERE (value) IN
(SELECT value FROM table_b);

+-------+
| value |
+-------+
| B     |
+-------+

Ответ 2

Существует более эффективный способ создания пересечения, используя UNION ALL и GROUP BY. Выступления в два раза лучше, чем мои тесты на больших наборах данных.

Пример:

SELECT t1.value from (
  (SELECT DISTINCT value FROM table_a)
  UNION ALL 
  (SELECT DISTINCT value FROM table_b)
) AS t1 GROUP BY value HAVING count(*) >= 2;

Это более эффективно, потому что с помощью решения INNER JOIN MySQL будет искать результаты первого запроса, а затем для каждой строки искать результат во втором запросе. С решением UNION ALL-GROUP BY он будет запрашивать результаты первого запроса, результаты второго запроса, а затем группировать результаты одновременно.

Ответ 3

В ваш запрос всегда будет возвращен пустой набор записей, так как cut_name= '全プロセス' and cut_name='恐慌' никогда не будет оцениваться до true.

В общем случае INTERSECT в MySQL следует эмулировать следующим образом:

SELECT  *
FROM    mytable m
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   (o.col1 = m.col1 OR (m.col1 IS NULL AND o.col1 IS NULL))
                AND (o.col2 = m.col2 OR (m.col2 IS NULL AND o.col2 IS NULL))
                AND (o.col3 = m.col3 OR (m.col3 IS NULL AND o.col3 IS NULL))
        )

Если обе таблицы имеют столбцы, помеченные как NOT NULL, вы можете опустить части IS NULL и переписать запрос с помощью немного более эффективного IN:

SELECT  *
FROM    mytable m
WHERE   (col1, col2, col3) IN
        (
        SELECT  col1, col2, col3
        FROM    othertable o
        )

Ответ 4

Я только что проверил его в MySQL 5.7 и действительно удивился, как никто не предложил простой ответ: NATURAL JOIN

Если таблицы или (выберите результат) имеют ИДЕНТИЧЕСКИЕ столбцы, вы можете использовать NATURAL JOIN как способ найти пересечение:

Например:

table1

id, name, jobid

'1', 'John', '1'

'2', 'Jack', '3'

'3', 'Adam', '2'

'4', 'Bill', '6'

table2

id, name, jobid

'1', 'John', '1'

'2', 'Jack', '3'

'3', 'Adam', '2'

'4', 'Bill', '5'

'5', 'Max', '6'

И вот запрос:

SELECT * FROM table1 NATURAL JOIN table2;

Результат запроса:  id, name, jobid

'1', 'John', '1'

'2', 'Jack', '3'

'3', 'Adam', '2'

Ответ 5

Для полноты здесь есть еще один метод эмуляции INTERSECT. Обратите внимание, что форма IN (SELECT ...), предложенная в других ответах, обычно более эффективна.

Обычно для таблицы с именем mytable с первичным ключом называется id:

SELECT id
FROM mytable AS a
INNER JOIN mytable AS b ON a.id = b.id
WHERE
(a.col1 = "someval")
AND
(b.col1 = "someotherval")

(Обратите внимание: если вы используете SELECT * с этим запросом, вы получите в два раза больше столбцов, которые определены в mytable, потому что INNER JOIN генерирует Декартовский продукт)

Здесь INNER JOIN генерирует каждые permutation пары строк из вашей таблицы. Это означает, что каждая комбинация строк генерируется в любом возможном порядке. Предложение WHERE затем фильтрует сторону a пары, а затем сторону b. Результатом является то, что возвращаются только строки, удовлетворяющие обоим условиям, так же как и пересечение двух запросов.

Ответ 6

Разделите свою проблему в 2 операторах: во-первых, вы хотите выбрать все, если

(id=3 and cut_name= '全プロセス' and cut_name='恐慌')

истинно. Во-вторых, вы хотите выбрать все, если

(id=3) and ( cut_name='全プロセス' or cut_name='恐慌')

истинно. Итак, мы присоединяемся как к OR, потому что мы хотим выбрать все, если кто-либо из них прав.

select * from emovis_reporting
    where (id=3 and cut_name= '全プロセス' and cut_name='恐慌') OR
        ( (id=3) and ( cut_name='全プロセス' or cut_name='恐慌') )

Ответ 7

AFAIR, MySQL реализует INTERSECT через INNER JOIN.

Ответ 8

SELECT
  campo1,
  campo2,
  campo3,
  campo4
FROM tabela1
WHERE CONCAT(campo1,campo2,campo3,IF(campo4 IS NULL,'',campo4))
NOT IN
(SELECT CONCAT(campo1,campo2,campo3,IF(campo4 IS NULL,'',campo4))
FROM tabela2);