Alternative to Intersect in MySQL

intersect, mysql

Solution

Microsoft SQL Server's `INTERSECT` "returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand" This is different from a standard `INNER JOIN` or `WHERE EXISTS` query.

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

With this particular question, the id column is involved, so duplicate values will not be returned, but for the sake of completeness, here's a MySQL alternative using `INNER JOIN` and `DISTINCT`:

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

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

And another example using `WHERE ... IN` and `DISTINCT`:

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

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

Problem

I need to implement the following query in MySQL. ``` (select * from emovis_reporting where (id=3 and cut_name= '全プロセス' and cut_name='恐慌') ) intersect ( select * from emovis_reporting where (id=3) and ( cut_name='全プロセス' or cut_name='恐慌') ) ``` I know that intersect is not in MySQL. So I need another way. Please guide me.

Original source