Not IN嵌套SELECT不工作-没有返回结果-Postgres[重复]

Question

我的头撞在墙上。。我有两个表，订单和批次。我正在使用PostgreSQL 9。

这些查询按照我的预期工作并返回结果：

查询#1（正常工作，返回订单#12、#8等）：

SELECT "orders".* 
FROM "orders" 
    INNER JOIN "batches" ON "batches"."order_id" = "orders"."id" 
WHERE (order_id not IN (14,4,13,5,2,9,3,7,11));

查询#2（正常工作，返回具有批次的唯一订单ID，这些批次的估计结束日期晚于2019-08-06-与上面列出的数字相同）：

SELECT DISTINCT "batches"."order_id" 
FROM "batches" 
WHERE (estimated_end_date >= '2019-08-06');

但是，如果将select查询#2替换为数字列表，则返回的结果与查询#1的结果不同：

SELECT "orders".* 
FROM "orders" 
    INNER JOIN "batches" ON "batches"."order_id" = "orders"."id" 
WHERE (order_id not IN (SELECT DISTINCT "batches"."order_id"          
                                FROM "batches" 
                                WHERE (estimated_end_date >= '2019-08-06')))

我做错了什么？如何使嵌套子查询正常工作？谢谢您！

Answer 1

您可以转换此查询

SELECT "orders".* 
FROM orders 
    INNER JOIN "batches" ON "batches"."service_order_id" = "service_orders"."id" 
WHERE (service_order_id not IN (SELECT DISTINCT "batches"."service_order_id"          
                                FROM "batches" 
                                WHERE (estimated_end_date >= '2019-08-06')))

到联接

SELECT orders.* 
FROM orders 
INNER JOIN "batches" ON "batches"."service_order_id" = orders.id 
LEFT JOIN batches AS S ON s.service_order_id = orders.id and s.estimated_end_date >= '2019-08-06'
WHERE S.service_order_id is null

Answer 2

不要将not in与子查询一起使用。如果任何返回的值都是null，那么您将根本不会得到任何结果。改用不存在：

SELECT so.* 
FROM service_orders so INNER JOIN
     batches b
     ON b.service_order_id = so.id
WHERE NOT EXISTS (SELECT          
                  FROM batches b2
                  WHERE b2.estimated_end_date >= '2019-08-06' AND
                        b2.service_order_id = b.service_order_id
                );

有更简单的方法来表达这种逻辑，例如使用窗口函数：

SELECT so.* 
FROM service_orders so INNER JOIN
     (SELECT b.*,
             MAX(b.estimated_end_date) OVER (PARTITION BY b.service_order_id) as max_eed
      FROM batches b
     ) b
     ON b.service_order_id = so.id
WHERE max_eed <= '2019-08-06' ;

Answer 3

添加条件：

"batches"."order_id" IS NOT NULL

在子查询中避免将order_id与nulls进行比较：

SELECT "service_orders".* 
FROM "service_orders" INNER JOIN "batches" 
ON "batches"."order_id" = "orders"."id" 
WHERE order_id not IN (
  SELECT DISTINCT "batches"."order_id"          
  FROM "batches" 
  WHERE estimated_end_date >= '2019-08-06' AND "batches"."order_id" IS NOT NULL
)