Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Alternative to Intersect in MySQL

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.

like image 268
Ankur Jariwala Avatar asked Apr 12 '10 10:04

Ankur Jariwala


People also ask

What can I use instead of INTERSECT in MySQL?

Since MySQL does not provide support for the INTERSECT operator. However, we can use the INNER JOIN and IN clause to emulate this operator.

Is UNION and INTERSECT same in SQL?

The UNION operation combines the results of two subqueries into a single result that comprises the rows that are returned by both queries. The INTERSECT operation combines the results of two queries into a single result that comprises all the rows common to both queries.

Does MySQL Workbench support INTERSECT?

Note that MySQL does not support the INTERSECT operator. This tutorial introduces you to how to emulate the INTERSECT operator in MySQL using join clauses.

Is INTERSECT and inner join same in SQL?

They are very different, even in your case. The INNER JOIN will return duplicates, if id is duplicated in either table. INTERSECT removes duplicates. The INNER JOIN will never return NULL , but INTERSECT will return NULL .


2 Answers

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     | +-------+ 
like image 198
Mike Avatar answered Oct 17 '22 19:10

Mike


There is a more effective way of generating an intersect, by using UNION ALL and GROUP BY. Performances are twice better according to my tests on large datasets.

Example:

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; 

It is more effective, because with the INNER JOIN solution, MySQL will look up for the results of the first query, then for each row, look up for the result in the second query. With the UNION ALL-GROUP BY solution, it will query results of the first query, results of the second query, then group the results all together at once.

like image 30
FBB Avatar answered Oct 17 '22 20:10

FBB