How Can I Replicate PostgreSQL's INTERSECT Functionality in MySQL?

MySQL Equivalents of PostgreSQL INTERSECT

In MySQL, the INTERSECT operator is not supported. However, there are several alternative approaches to achieve similar results.

For instance, when searching across multiple fields for records that satisfy specific criteria, an inner join can be employed. The following query filters for rows in the "records" table that have matching rows in the "data" table, where "john" and "smith" correspond to values for the "firstname" and "lastname" fields, respectively:

SELECT DISTINCT records.id
FROM records
INNER JOIN data d1 ON d1.id = records.firstname AND d1.value = "john"
INNER JOIN data d2 ON d2.id = records.lastname AND d2.value = "smith"

Another alternative is the IN clause:

SELECT DISTINCT records.id
FROM records
WHERE records.firstname IN (
    SELECT id FROM data WHERE value = 'john'
)
AND records.lastname IN (
    SELECT id FROM data WHERE value = 'smith'
)

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