Intersect in mysql

Question

Hello everyone I need to implement 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 m Mysql. So I need alternate way. Please guide me....

Answer 1

well, i don't know about mysql, but this particular query doesn't require intersect. Remeber set theory. First query is exact subset of second (A & B & C and A & (B v C) ) so result will be always same as first only.

Answer 2

Your query would always return an empty recordset since cut_name= '全プロセス' and cut_name='恐慌' will never evaluate to true.

In general, INTERSECT in MySQL should be emulated like this:

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

If both your tables have columns marked as NOT NULL, you can omit the IS NULL parts and rewrite the query with a slightly more efficient IN:

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

Answer 3

AFAIR, MySQL implements INTERSECT through INNER JOIN.

Answer 4

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

Answer 5

UNION is MySQL-speak for INTERSECT in this context.

SELECT *
     FROM emovis_reporting
     WHERE (id=3 AND cut_name= '全プロセス' AND cut_name='恐慌')
UNION
SELECT *
    FROM emovis_reporting
    WHERE (id=3) AND (cut_name='全プロセス' OR cut_name='恐慌')