How to Replicate MySQL's INTERSECT Operator Functionality?

Equivalents to MySQL's INTERSECT Operator

The SQL INTERSECT operator is used to identify and return the matching values that occur in both sections of the query. However, this operator is not natively supported in MySQL.

Alternative Approaches

To achieve the functionality of the INTERSECT operator in MySQL, several alternative methods can be employed:

• INNER JOIN Using DISTINCT:

SELECT DISTINCT table_a.column
FROM table_a
INNER JOIN table_b ON table_a.column = table_b.column;

• WHERE ... IN and DISTINCT:

SELECT DISTINCT table_a.column
FROM table_a
WHERE table_a.column IN (SELECT table_b.column FROM table_b);

Example

Consider the following query using the INTERSECT operator:

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

This query retrieves records from the 'emovis_reporting' table where the id is 3 and the cut_name is both '全プロセス' and '恐慌'.

MySQL Equivalents

To implement this query in MySQL using the INNER JOIN method:

SELECT DISTINCT *
FROM emovis_reporting AS a
INNER JOIN emovis_reporting AS b ON a.id = b.id
WHERE a.id = 3
  AND a.cut_name = '全プロセス'
  AND b.cut_name = '恐慌';

Using the WHERE ... IN and DISTINCT method:

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

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