How to Replicate the INTERSECT Operator's Functionality in MySQL?

Alternatives to the Intersect Operator in MySQL

The INTERSECT operator, found in Microsoft SQL Server, returns duplicate values that appear in both query results. MySQL lacks this operator, necessitating an alternative approach.

MySQL Alternatives

To achieve the same functionality as INTERSECT in MySQL, consider using:

• INNER JOIN with DISTINCT: Selects distinct values from a join of the two tables.

SELECT DISTINCT value FROM table_a
INNER JOIN table_b
USING (value);

• WHERE ... IN with DISTINCT: Filters records in the first table by values present in the second table.

SELECT DISTINCT value FROM table_a
WHERE (value) IN (SELECT value FROM table_b);

Example Query

Consider the following query:

(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 with DISTINCT:

SELECT DISTINCT * FROM emovis_reporting
INNER JOIN (SELECT * FROM emovis_reporting WHERE ID=3 AND cut_name='全プロセス') AS t1
ON cut_name='恐慌';

MySQL Alternative Using WHERE ... IN with DISTINCT:

SELECT DISTINCT * FROM emovis_reporting
WHERE (id, cut_name) IN (
  SELECT id, cut_name FROM emovis_reporting
  WHERE>

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