带并的MySQL误差

Question

下面的sql查询有什么问题？

select itemname from Item where itemid 
in

((select ItemID
 FROM Delivery NATURAL JOIN Supplier
 WHERE SupplierName = 'Nepalese Corp.')

 union

 (select ItemID
 FROM Sale NATURAL JOIN Department
 WHERE DepartmentName = 'Navigation'))

我在这个网站上看到了另一篇文章，它建议删除两个联合集上的内括号，并给第一个联合集一个别名。我试过了，MYSQL在下面所示的行中显示了x，但是查询运行得很好。我的问题是发生了什么？

select itemname from Item where itemid 
in

(select ItemID as id
 FROM Delivery NATURAL JOIN Supplier
 WHERE SupplierName = 'Nepalese Corp.'

 union

 select ItemID .          //shows an x at this line
 FROM Sale NATURAL JOIN Department
 WHERE DepartmentName = 'Navigation')

Answer 1

select itemname from Item 
where itemid in
(
    select ItemID 
    FROM Delivery 
    NATURAL JOIN Supplier WHERE SupplierName = 'Nepalese Corp.'
    union
    select ItemID
    FROM Sale 
    NATURAL JOIN Department WHERE DepartmentName = 'Navigation'
 )

你可以试试上面的代码。

只要去掉不必要的括号就可以解决你的问题。

Answer 2

您可以尝试将内部结果连接到主表。

 select itemname from Item as t1 join
    (select distinct ItemID
     FROM Delivery NATURAL JOIN Supplier
     WHERE SupplierName = 'Nepalese Corp.'
    union
    select distinct ItemID
     FROM Sale NATURAL JOIN Department
     WHERE DepartmentName = 'Navigation') as t2 on t1.ItemID=t2.ItemID