mysql intersect

Question

I'm new to mysql so please be kind. I'm getting the following error for my script and im not sure whats wrong with it.

SELECT uoid 
FROM mint 
WHERE mint_id='6' and userid='3836'
INTERSECT 
SELECT id as uoid
FROM cats 
WHERE category='Health, Fitness' 
ORDER BY 1;

gives

#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 id as uoid FROM cats WHERE category='Health, Fitness'' at line 4

That is because MySql does not have a INTERSECT keyword. Possibly interesting: http://stackoverflow.com/questions/2621382/intersect-in-mysql. — alexn, May 03 '12 at 14:35

score 6 · Accepted Answer · edited May 23 '17 at 11:48

6

MySQL does not have an INTERSECT keyword. See this question and this one for suggestions about how to achieve what you're after.

edited May 23 '17 at 11:48

Community

1
1

answered May 03 '12 at 14:37

pjmorse

9,204
9
54
124

Thank you! I'll give it to you since the links you gave helped fix my problem – John May 03 '12 at 14:49

score 1 · Answer 2 · answered May 03 '12 at 14:37

1

INTERSECT is not supported in MySQL. You need to restructure your query somehow. You might be able to use a subquery if you version of MySQL supports subqueries.

answered May 03 '12 at 14:37

Justin Ethier

131,333
52
229
284

score 0 · Answer 3 · answered Jun 09 '15 at 07:46

0

This is because intersect only work on same table, not on different tables as you are trying. See this

In place of intersect use join statement syntax. And INTERSECT does exist in sql

answered Jun 09 '15 at 07:46

Nikhil Pareek

734
9
24

score 0 · Answer 4 · answered Nov 27 '22 at 16:08

The INTERSECT operator has become available for MySQL since version 8.0.31, released in 10 November 2022. You can now do the following:

SELECT uoid FROM mint WHERE mint_id='6' and userid='3836'
INTERSECT 
SELECT id as uoid FROM cats WHERE category='Health, Fitness' 
ORDER BY 1;

mysql intersect

4 Answers4

Linked