SQL选择名称相同但价格不同的行

Question

我有两个表COMMODITY_New和COMMODITY_Old，每个表都有一些行。我需要从COMMODITY_New中选择在COMMODITY_Old.NAME中具有名称但具有不同价格的行。

示例：

COMMODITY_New：

+------+------+
|Name  |Price |
+------+------+
|Apple |200   |
|Socks |100   |
+------+------+

COMMODITY_Old：

+------+------+
|Name  |Price |
+------+------+
|Apple |180   |
|Socks |100   |
+------+------+

新的价格并不总是更高。

我的SQL代码：

SELECT 
    NAME, PRICE
FROM 
    COMMODITY_New
WHERE 
    COMMODITY_New.NAME IN (SELECT NAME FROM somedb2.COMMODITY_Old) 
    AND COMMODITY_New.PRICE = (SELECT PRICE FROM somedb2.COMMODITY_Old)

Answer 1

您可以使用EXISTS

select n.*
from commodity_new n
where exists (select 1
              from commodity_old o
              where o.name = n.name and o.price <> n.price
             );

Answer 2

只需通过name连接这两个表，并选择具有不同价格的行。

SELECT o.name, o.price, n.price 
FROM commodity_old o INNER JOIN commodity_new n ON o.name = n.name
WHERE o.price <> n.price;