SQL: Syntax error with intersect?

Question

This is my query:

-- Sids of suppliers who supply a green part AND a red part
(SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color = "red")
INTERSECT
(SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color = "green");

This is the error:

ERROR 1064 (42000): 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 Suppliers.sid FROM Suppliers JOIN Catalog ON Catalog.sid = Sup" on line 6.

What am I doing wrong?

This is the schema:

Suppliers(sid: integer, sname: string, address string)

Parts(pid: integer, pname: string, color: string)

Catalog(sid: integer, pid: integer, cost: real)

bold = primary key

Boarding

INTERSECT was added in MySQL 8.0.31. dev.mysql.com/doc/refman/8.0/en/intersect.html – Tabitha

Answer 1

MySQL, which you appear to be using, does not support the INTERSECT syntax. You're going to have to solve it another way.

In this case, it is trivial -we only need a list of all suppliers that offer "green" and "red" of some part- your query does not bother to see if the parts themselves are related, so we can solve it quite easily like this:

SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color IN ('red', 'green')
GROUP BY Suppliers.sid
HAVING COUNT(DISTINCT Parts.color) = 2

Personally, I don't believe the original query is a typical INTERSECT problem. Take a look at the JOIN solution offered by Vinko Vrsalovic for a general solution to emulate the INTERSECT (which I would btw prefer even if the RDBMS would in fact offer INTERSECT natively).

Globe

It would be interesting to see which is faster, this query or my query -- I think it comes down to which is faster, a 2nd join or a group by operation. – Douzepers

@Hogan, shrug. This is dependent upon so many things: indexes, which engine, how many rows, how many memory. enless list...if the question would have been to find the fastest solution, I would have taken an entirely differernt approach to answering this. For now, I am in the mode of: how to rewrite standard SQL to get an equivalent result in case MySQL doens't happen to support the syntax. – Globe

point taken, I guess I'm always in the mode of what is fastest, not what is clearest. – Douzepers

Answer 2

Nothing, MySQL doesn't have the INTERSECT keyword. You can rewrite it as an INNER JOIN:

SELECT DISTINCT sid FROM
(SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color = "red") a
INNER JOIN
(SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color = "green") b
ON (a.sid = b.sid);

This query can surely be better written, but this is to show that intersect is but merely an inner join with a select distinct, you can automatically transform one into the other.

Taskmaster

I mostly agree, but INTERSECT is not exactly the same as an INNER JOIN. INTERSECT defaults to INTERSECT DISTINCT, which means it is required to return only unique rows. In practical cases and probably in this case too, it would just work, but to get a true rewrite, you woudl have to add either DISTINCT or GROUP BY – Globe

all cool. You're geting +1 now, because i basically think JOIN is much, much nicer than INTERSECT :) Only time I ever saw intersect, is in SQL homework assignments ;) – Globe

For example, the two other answers here which are built as a single query and have less total joins. – Taskmaster

Answer 3

This should do what you want:

SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
INNER JOIN Parts AS parts1 ON parts1.pid = Catalog.pid AND parts1.color = "red"
INNER JOIN Parts AS parts2 ON parts2.pid = Catalog.pid AND parts2.color = "green"

Douzepers

I don't think that's the problem in this case. INTERSECT simply isn't supported by MySQL. – Globe

And that query won't work out. The OP wants all sids where they have a green AND a red part. Your query returns all sids with either a green OR a red part. – Taskmaster

@Roland, @Vinko : Yeah I saw that, I fixed the answer, this should do what he wants and be faster than a subquery. – Douzepers

Answer 4

Another solution in order to use INTERSECT in MySQL is to use IN clause. Problem: "Find course id’s of courses offered in Fall 2009 and Spring 2010"

//DML sample
(select course_id
from section
where semester = ‘Fall’ and year = ‘2009’)
intersect
(select course_id
from section
where semester = ‘Spring’ and year = ‘2010’);

In MySQL:

select distinct course_id
from section
where semester = 'Fall' and year= 2009 and
course_id in (select course_id
from section
where semester = 'Spring' and year= 2010);

If you need more on IN clause , please search on Google.

Nedneda

Answer 5

I think SQL doesn't support INTERESCT

try this one

SELECT DISTINCT
s.sid
FROM
 suppliers s,
 catalog c
WHERE
 s.sid = c.sid
 AND c.pid IN (SELECT 
 p1.pid
 FROM
 parts p1,
 parts p2
 WHERE
 p1.color = 'red' AND p2.color = 'green');

Shannanshannen

Answer 6

Description:

Suppose we have two DB tables T1 and T2 and we need common elements from both tables then we can use INTERSECT Operation as shown in the above image.

In MySql there is no INTERSECT operator. So we can implement INTERSECT concept with following two operators:

1. IN Clause
We can use IN clause when we want one column as of result of INTERSECT Operation.

2. EXISTS Clause
We can use EXISTS clause when we want multiple column as of result of INTERSECT Operation.

For more details you can read the documentation here.

Answer:

In above asked question they want single column as of the result, so we can use IN Clause as follows:

SELECT Suppliers.sid FROM Suppliers 
INNER JOIN Catalog ON Catalog.sid = Suppliers.sid 
INNER JOIN Parts ON Parts.pid = Catalog.pid 
WHERE Parts.color = "red"
AND Suppliers.sid IN (
    SELECT Suppliers.sid
    FROM Suppliers
    INNER JOIN Catalog ON Catalog.sid = Suppliers.sid
    INNER JOIN Parts ON Parts.pid = Catalog.pid
    WHERE Parts.color = "green")

Logging