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

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

SQL-сервер

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     |
+-------+

Существует более эффективный способ создания пересечения с использованием 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 он будет запрашивать результаты первого запроса, результаты второго запроса, а затем группировать все результаты одновременно.

Ваш запрос всегда будет возвращать пустой набор записей, поскольку 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
        )

Я только что проверил это в MySQL 5.7 и очень удивлен тем, что никто не предложил простого ответа: ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ

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

Например:

таблица 1:

id, имя, jobid

«1», «Джон», «1»

«2», «Джек», «3»

«3», «Адам», «2»

«4», «Билл», «6»

таблица2:

id, имя, jobid

«1», «Джон», «1»

«2», «Джек», «3»

«3», «Адам», «2»

«4», «Билл», «5»

«5», «Макс.», «6»

И вот запрос:

SELECT * FROM table1 NATURAL JOIN table2;

Результат запроса: идентификатор, имя, идентификатор задания

«1», «Джон», «1»

«2», «Джек», «3»

«3», «Адам», «2»

Для полноты вот еще один метод эмуляции 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 генерирует каждую перестановку пар строк из вашей таблицы. Это означает, что генерируются все комбинации строк в любом возможном порядке. Предложение WHERE затем фильтрует сторону a пары, затем сторону b. В результате возвращаются только те строки, которые удовлетворяют обоим условиям, как и в случае пересечения двух запросов.

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

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

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

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

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

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

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