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:
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!