How Can I Replicate MySQL's `INTERSECT` Functionality?

Finding Equivalents for MySQL's Intersect

In SQL, the INTERSECT operator retrieves distinct values that appear in both specified queries. However, MySQL does not natively support INTERSECT.

Alternatives in MySQL

To achieve a similar result in MySQL, one can use:

• INNER JOIN: Per column values match between both tables, but duplicates will be returned.
• WHERE ... IN: Selects distinct values within the first table that also exist within the second table.

Example

Consider the following query in another database management system that utilizes INTERSECT:

(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='恐慌') )

MySQL Alternative using INNER JOIN:

SELECT DISTINCT * 
FROM emovis_reporting e1
INNER JOIN emovis_reporting e2 
ON (e1.id = e2.id) AND (e1.cut_name = e2.cut_name)
WHERE e1.id = 3 AND (e1.cut_name = '全プロセス' OR e1.cut_name = '恐慌');

MySQL Alternative using WHERE ... IN:

SELECT DISTINCT * 
FROM emovis_reporting
WHERE (id = 3) AND (cut_name IN ('全プロセス', '恐慌'));

These MySQL alternatives return distinct values that satisfy the conditions specified in both subqueries. However, it's important to note that the id column is involved in this specific query, which will prevent duplicates from being returned. For queries without unique identifiers, using DISTINCT in the outer query may be necessary to ensure unique results.

The above is the detailed content of How Can I Replicate MySQL's `INTERSECT` Functionality?. For more information, please follow other related articles on the PHP Chinese website!