SQL：intersect出现语法错误？

Question

这是我的查询：

-- 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");

以下是错误信息:

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.

我做错了什么？

这是数据库模式：

Suppliers(sid: 整型, sname: 字符串, address 字符串)

Parts(pid: 整型, pname: 字符串, color: 字符串)

Catalog(sid: 整型, pid: 整型, cost: 浮点数)

粗体 = 主键

- Nick Heiner

---

INTERSECT 在 MySQL 8.0.31 中被添加。 https://dev.mysql.com/doc/refman/8.0/en/intersect.html - Louis Huang

Answer 1

你似乎在使用MySQL，它不支持INTERSECT语法。你需要用另一种方式解决它。

在这种情况下，问题很简单-我们只需要获取所有供应商的列表，这些供应商提供了某些零件的“绿色”和“红色”，而你的查询并没有关心零件本身是否相关，因此我们可以很容易地像这样解决：

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

就我个人而言，我认为原始查询不是一个典型的INTERSECT问题。请看Vinko Vrsalovic提供的JOIN解决方案，用于模拟INTERSECT（即使RDBMS实际上提供了INTERSECT，我也更喜欢这种方法）。

- Roland Bouman

---

看看哪个更快，这个查询还是我的查询会很有趣——我认为关键在于哪个更快，第二个连接还是分组操作。 - Hogan

@Hogan，耸肩。这取决于很多因素：索引、引擎、行数、内存大小等等。无穷无尽的列表...如果问题是要找到最快的解决方案，我会采取完全不同的方法来回答这个问题。现在，我的思路是：如何重写标准SQL以获得等效的结果，以防MySQL不支持该语法。 - Roland Bouman

我明白了，我想我总是处于最快速度的模式下，而不是最清晰的模式。 - Hogan

Answer 2

很抱歉，MySQL没有INTERSECT关键字。您可以将其重写为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);

这个查询肯定可以更好地编写，但是这只是为了说明交集仅仅是使用选择不同的内连接，你可以自动将一个转换为另一个。

- Vinko Vrsalovic

---

我基本上同意，但是INTERSECT并不完全等同于INNER JOIN。 INTERSECT默认为INTERSECT DISTINCT，这意味着必须仅返回唯一的行。在实际情况下，可能在这种情况下也可以正常工作，但要进行真正的重写，您需要添加DISTINCT或GROUP BY。 - Roland Bouman

都很酷。你现在得到了+1，因为我基本上认为JOIN比INTERSECT好多了:) 我只在SQL作业中看到过INTERSECT。 - Roland Bouman

例如，这里的另外两个答案是作为单个查询构建的，并且具有较少的总连接数。 - Vinko Vrsalovic

Answer 3

这应该可以满足您的需求：

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"

- Hogan

---

在这种情况下，我认为那不是问题所在。MySQL根本不支持INTERSECT。 - Roland Bouman

那个查询不会起作用。OP想要所有具有绿色和红色部分的sids。您的查询返回具有绿色或红色部分的所有sids。 - Vinko Vrsalovic

@Roland，@Vinko：是的，我看到了，我修正了答案，这应该能够满足他的需求，并且比子查询更快。 - Hogan

Answer 4

在MySQL中使用INTERSECT的另一种解决方案是使用IN子句。问题："查找在2009年秋季和2010年春季开设的课程id"

//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’);

在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);

如果您需要更多关于IN子句的信息，请在Google上搜索。

- Jasurbek Nabijonov

Answer 5

我认为SQL不支持INTERSECT

试试这个

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');

- Khushboo Kumari

Answer 6

描述：

假设我们有两个数据库表T1和T2，我们需要从这两个表中获取共同的元素，那么我们可以使用上面图片中显示的INTERSECT操作。

在MySql中没有INTERSECT运算符。因此，我们可以使用以下两个运算符来实现INTERSECT概念：

1. IN子句 当我们想要一个列作为INTERSECT操作结果时，我们可以使用IN子句。

2. EXISTS子句 当我们想要多个列作为INTERSECT操作结果时，我们可以使用EXISTS子句。

更多详细信息，请阅读文档here。

答案： 在上述问题中，他们希望将单个列作为结果，因此我们可以使用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 = "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")

- Haresh Kumar