Alternative to Intersect in MySQL

Question

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.

Knack

cut_name cannot have two different values for any given row, so the first "select" won't return anything. – Vincenz

Answer 1

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

Natty

why the parentheses around value? – Touchback

Answer 2

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.

Criminate

UNION ALL doesn't remove duplicates, I think using UNION in this case will be better to get the accurate intersection. – Although

@Although : that's the point, you do not want to remove duplicates, to be able to count number of results, and to satisfy the clause HAVING count(*) >= 2. If you do not use UNION ALL, this will not work. – Criminate

Doesn't mysql actually have any query optimization of its own, that would pick the better execution path for these comparable queries? – Touchback

@Barmar: no it won't, since we use the DISTINCT clause in each of the subqueries – Criminate

@Barmar Ha, my bad! – Criminate

Answer 3

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
        )

Productive

Answer 4

I just checked it in MySQL 5.7 and am really surprised how no one offered a simple answer: NATURAL JOIN

When the tables or (select outcome) have IDENTICAL columns, you can use NATURAL JOIN as a way to find intersection:

For example:

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'

And here is the query:

SELECT * FROM table1 NATURAL JOIN table2;

Query Result: id, name, jobid

'1', 'John', '1'

'2', 'Jack', '3'

'3', 'Adam', '2'

Dillie

This answer is incorrect. A NATURAL JOIN does not perform an intersect. Per MySQL doc: The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables. – Criminate

Answer 5

For completeness here is another method for emulating INTERSECT. Note that the IN (SELECT ...) form suggested in other answers is generally more efficient.

Generally for a table called mytable with a primary key called 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")

(Note that if you use SELECT * with this query you will get twice as many columns as are defined in mytable, this is because INNER JOIN generates a Cartesian product)

The INNER JOIN here generates every permutation of row-pairs from your table. That means every combination of rows is generated, in every possible order. The WHERE clause then filters the a side of the pair, then the b side. The result is that only rows which satisfy both conditions are returned, just like intersection two queries would do.

Adenovirus

Answer 6

Starting from MySQL 8.0.31 the INTERSECT is natively supported.

INTERSECT Clause:

SELECT ... INTERSECT [ALL | DISTINCT] SELECT ... [INTERSECT [ALL | DISTINCT] SELECT ...]

INTERSECT limits the result from multiple SELECT statements to those rows which are common to all.

Sample:

 SELECT 1 AS col
 INTERSECT 
 SELECT 1 AS col;

 -- output
 1

Dipnoan

Answer 7

I use IN to make Intersection

This is an example of usage:

SELECT * FROM `test_oc_product` 
WHERE product_id IN ( SELECT product_id FROM test_oc_product_option WHERE option_id = '21' AND value = 'Red'  )
AND product_id IN ( SELECT product_id FROM test_oc_product_attribute WHERE attribute_id = '10' )

And here is dump

CREATE TABLE `test_oc_product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(64) NOT NULL,
  `sku` varchar(64) NOT NULL,
   PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_oc_product` (`product_id`, `model`, `sku`) VALUES
(1, 'A1', 'A1'),
(2, 'A2', 'A2'),
(3, 'A3', 'A3'),
(4, 'AA', 'AA'),
(5, 'A5', 'A5'),
(6, 'A6', 'A6'),
(7, 'A7', 'A7'),
(8, 'A8', 'A8');

CREATE TABLE `test_oc_product_attribute` (
  `product_id` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`product_id`, `attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_oc_product_attribute` (`product_id`, `attribute_id`, `text`) VALUES 
('1', '10', 'Attribute Value 1'), 
('2', '11', 'Attribute Value 2');


CREATE TABLE `test_oc_product_option` (
  `product_option_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  `value` text NOT NULL,
   PRIMARY KEY (`product_option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `test_oc_product_option` (`product_option_id`, `product_id`, `option_id`, `value`) VALUES 
(NULL, '1', '21', 'Red'), 
(NULL, '2', '21', 'Red'),
(NULL, '3', '21', 'Blue'),
(NULL, '4', '10', 'S');

Asquint

Answer 8

Break your problem in 2 statements: firstly, you want to select all if

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

is true . Secondly, you want to select all if

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

is true. So, we will join both by OR because we want to select all if anyone of them is true.

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

Refulgent

Answer 9

Emulation of INTERSECT via INNER JOIN or IN() can only work with few 2-4 joining sets. If you will try to find interaction between multiple sets, you need to use multiple INNER JOINs. But multiple INNER JOINs in MySQL is absolutely unstable solution which may leads entanglement of query plan executor which, in our case, just hang for several days with 100% CPU consumption.

You need to update MySQL to the version 8.0.31. It contains INTERSECT operator.

Nylons

Answer 10

AFAIR, MySQL implements INTERSECT through INNER JOIN.

Dubitable

No, an INNER JOIN produces a Cartesian product. That means that every permutation of (row-from-first-table, row-from-second-table) is generated. However, with an appropriate WHERE clause this can be used to apply the same logic as INTERSECT would, see my answer. – Adenovirus

But can't you just use an INNER JOIN with a USING clause and it would effectively be the same as an INTERSECT? – Tog

Answer 11

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

Washery

Regardless of this being correct or not, you should format your code in a code block and also describe how/why this solves the problem. – Prejudicial