Why MySQL don't recognize INTERSECT in a query

Question

i'm trying to do a query with the INTERSECT clause but PHPmyAdmin don’t recognize INTERSECT and this is the error :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT (SELECT users.name, users.id, users.email FROM users ' at line 10

I can't understand and i haven't found a problem like this. So any solutions please ? Thanks

SELECT  users.name, users.id, users.email

        FROM users
        WHERE users.id NOT IN (
    SELECT users.id
    FROM users
    JOIN clubs_messages ON users.id = clubs_messages.id_user
    GROUP BY clubs_messages.id_user)

    INTERSECT

    SELECT  users.name, users.id, users.email
        FROM users
        WHERE users.id NOT IN (
    SELECT users.id
    FROM users
    JOIN sessions_messages ON users.id = sessions_messages.id_user
    GROUP BY sessions_messages.id_user)

Answer 1

intersect is not a mysql function. This has not so much to do with phpmyadmin, as it is returning an error which is correct in this case.

A quick google would help you rewrite your code:

from a random blog

SELECT member_id, name FROM a
INTERSECT
SELECT member_id, name FROM b

can simply be rewritten to

SELECT a.member_id, a.name
FROM a INNER JOIN b
USING (member_id, name)

Answer 2

INTERSECT is part of standard SQL, but MySQL does not support that operation. Many parts of SQL are optional for vendors. No implementation of SQL supports every part of the language.

Support for INTERSECT has been requested in the past: https://bugs.mysql.com/bug.php?id=31336

You may log in on bugs.mysql.com and click the "affects me" button for that issue, which they might use as an indication that there is demand for that feature.

---

Update: MySQL 8.0.31 (released 2022-10-11) supports INTERSECT. See https://lefred.be/content/intersect-and-except-in-mysql-8-0/

Answer 3

The INTERSECT keyword has been added to MySQL, but only since version 8.0.31: https://dev.mysql.com/doc/refman/8.0/en/intersect.html. So your query should work fine with the latest versions.