英文:
Can't combine 'NOT IN' with CTE in a 'WHERE' clause
问题
Solution 2 worked because it correctly embedded the subquery within the NOT IN clause of the main query. This ensured that the subquery was executed for each row in the main query, providing the expected filtering behavior.
In Solution 1, you attempted to use a Common Table Expression (CTE) named CTE, but you didn't reference it correctly within the NOT IN clause. To make Solution 1 work, you should have used it like this:
WITH CTE AS
(
SELECT DISTINCT PRODUCT_ID
FROM Order_Tbl
WHERE ORDER_DAY < '2015-05-02'
)
SELECT *
FROM Order_Tbl
WHERE ORDER_DAY = '2015-05-02'
AND PRODUCT_ID NOT IN (SELECT PRODUCT_ID FROM CTE)
The key nuance here is that SQL needs to understand the relationship between the subquery and the main query. In Solution 2, the subquery is directly embedded in the WHERE clause, so SQL knows that it should be evaluated for each row in the main query's result set. In Solution 1, you needed to explicitly reference the CTE within the subquery for it to work correctly.
英文:
INPUT: this is the Order_Tbl table with each row equivalent to a record of a transaction:
| ORDER_DAY | ORDER_ID | PRODUCT_ID | QUANTITY | PRICE |
|---|---|---|---|---|
| 2015-05-01 | ODR1 | PROD1 | 5 | 5 |
| 2015-05-01 | ODR2 | PROD2 | 2 | 10 |
| 2015-05-01 | ODR3 | PROD3 | 10 | 25 |
| 2015-05-01 | ODR4 | PROD1 | 20 | 5 |
| 2015-05-02 | ODR5 | PROD3 | 5 | 25 |
| 2015-05-02 | ODR6 | PROD4 | 6 | 20 |
| 2015-05-02 | ODR7 | PROD1 | 2 | 5 |
| 2015-05-02 | ODR8 | PROD5 | 1 | 50 |
| 2015-05-02 | ODR9 | PROD6 | 2 | 50 |
| 2015-05-02 | ODR10 | PROD2 | 4 | 10 |
EXPECTED OUTPUT: the task is to write a T-SQL query to get products that was ordered on 02-May-2015 but not on any other days before that:
| ORDER_DAY | ORDER_ID | PRODUCT_ID | QUANTITY | PRICE |
|---|---|---|---|---|
| 2015-05-02 | ODR6 | PROD4 | 6 | 20 |
| 2015-05-02 | ODR8 | PROD5 | 1 | 50 |
| 2015-05-02 | ODR9 | PROD6 | 2 | 50 |
I did try 2 solutions with a same approach: using a (completely identical) subquery to get a list of distinct products that were ordered before 02-May-2015, and then somehow putting it after the NOT IN operator inside the WHERE clause of the main query.
Solution 1: the subquery was passed in as an CTE. It throws a syntax error ...
WITH CTE AS
(
SELECT DISTINCT PRODUCT_ID
FROM Order_Tbl
WHERE ORDER_DAY < '2015-05-02'
)
SELECT *
FROM Order_Tbl
WHERE ORDER_DAY = '2015-05-02'
AND PRODUCT_ID NOT IN CTE
Solution 2: the subquery was embedded into the WHERE clause of the main query. This worked!
SELECT *
FROM Order_Tbl
WHERE ORDER_DAY = '2015-05-02'
AND PRODUCT_ID NOT IN (SELECT DISTINCT PRODUCT_ID
FROM Order_Tbl
WHERE ORDER_DAY < '2015-05-02')
What was the nuance that made SQL behave and return different results? I would appreciate it if you guys could give me a clear explanation as well as some useful notes for further SQL implementations.
答案1
得分: 1
以下是翻译好的内容：
任务是编写一个 T-SQL 查询，以获取在 2015 年 5 月 2 日订购的产品，但在此之前的任何其他日期都没有订购。
Thom A 在评论中解释了 NOT IN 不接受您尝试的第一种语法，即使使用正确的语法，一般来说，NOT EXISTS 优于 NOT IN：
select *
from order_tbl o
where order_day = '2015-05-02'
and not exists (
select 1
from order_tbl o1
where o1.product_id = o.product_id and o1.order_day < o.order_day
)
该查询确保在表中没有相同产品和更早订单日期的行。为了提高性能，考虑在 order_tbl(product_id, order_day) 上创建索引。
但总体而言，使用窗口函数可能更简单和更有效；子查询可以用窗口 min() 替代：
select *
from (
select o.*, min(order_day) over(partition by product_id) min_order_day
from order_tbl o
) o
where order_day = '2015-05-02' and min_order_day = order_day
希望这些信息对您有所帮助。
英文:
> The task is to write a T-SQL query to get products that was ordered on 02-May-2015 but not on any other days before that
It has been explained by Thom A in the comments that NOT IN does not accept the first syntax you tried, and that, even with the right syntax, NOT EXISTS is in general preferable to NOT IN:
select *
from order_tbl o
where order_day = '2015-05-02'
and not exists (
select 1
from order_tbl o1
where o1.product_id = o.product_id and o1.order_day < o.order_day
)
The query ensures that there is no row in the table for the same product and an erlier order date. For performance, consider an index on order_tbl(product_id, order_day).
But overall, it is probably simpler and more efficient to use window functions ; the subquery can just be replaced with a window min():
select *
from (
select o.*, min(order_day) over(partition by product_id) min_order_day
from order_tbl o
) o
where order_day = '2015-05-02' and min_order_day = order_day
答案2
得分: 0
In fact, CTE（通用表达式）允许您定义一个临时的命名结果集，该结果集在临时可用，您应该将其视为一个表，并以这种方式查询和编写您的代码。
select *
from Order_Tbl
where ORDER_DAY = '2015-05-02'
and PRODUCT_ID not in (
select distinct PRODUCT_ID
from Order_Tbl
where ORDER_DAY < '2015-05-02'
)
英文:
In fact, CTE A CTE allows you to define a temporary named result set that available temporarily, you should treat it like a table and query and write your code in this way
select *
from Order_Tbl
where ORDER_DAY = '2015-05-02'
and PRODUCT_ID not in (
select distinct PRODUCT_ID
from Order_Tbl
where ORDER_DAY < '2015-05-02'
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论