这是具有联接的有效相关子查询吗？ [英] Is this a valid correlated sub query with joins?

问题描述

所以我有一个表 Car（Car_id，Value，Make），另一个表 Person（Person_id，Name）和将这两个表链接在一起的第三个表 PerCar（Car_id，Person_id）。

So I have a table Car(Car_id, Value, Make), another table Person(Person_id, Name) and a third table linking the two together PerCar(Car_id, Person_id).

所以我有这个相关的子查询：

So I have this correlated subquery:

SELECT MAKE, VALUE 
FROM CAR WHERE VALUE > (SELECT AVG(VALUE) FROM CAR C WHERE C.CAR_ID = CAR_ID);

因此，如果我也想显示此人的姓名，这是否也是有效的相关子查询：

So if I wanted to display the person's name also, is this also valid correlated sub query:

SELECT Car.Make, Car.Value, Person.Name 
FROM PerCar NATURAL JOIN Car 
NATURAL JOIN Person where Car.Value > (SELECT AVG(VALUE) FROM Car);

推荐答案

这不是相关子查询：

SELECT MAKE, VALUE 
FROM CAR
WHERE VALUE > (SELECT AVG(VALUE) FROM CAR C WHERE C.CAR_ID = CAR_ID);

始终限定查询中的所有列引用。

您认为 WHERE 子句是：

WHERE CAR.VALUE > (SELECT AVG(VALUE) FROM CAR C WHERE C.CAR_ID = CAR.CAR_ID);

但这确实是：

WHERE CAR.VALUE > (SELECT AVG(C.VALUE) FROM CAR C WHERE C.CAR_ID = C.CAR_ID);

没有对外部查询的引用，因此不相关。我会这样写：

There is no reference to the outer query, so this is not correlated. I would write this as:

SELECT C.MAKE, C.VALUE 
FROM CAR C
WHERE C.VALUE > (SELECT AVG(C2.VALUE) FROM CAR C2 WHERE C2.CAR_ID = C2.CAR_ID);

但是，这不太可能返回任何行。鉴于您缺乏问题陈述和表中的值，明智的查询将返回所有价值高于其平均制造价值的汽车。

However, it is unlikely that this will return any rows. Given your lack of problem statement and the values in your tables, a sensible query would return "all cars whose value is higher than the average for their make"

SELECT C.MAKE, C.VALUE 
FROM CAR C
WHERE C.VALUE > (SELECT AVG(C2.VALUE) FROM CAR C2 WHERE C2.MAKE = C.MAKE);

由于其余问题基于第一个查询为相关子查询，因此其余这个问题没有道理。

Because the rest of your question is predicated on the first query being a "correlated subquery", the rest of the question does not make sense.

这篇关于这是具有联接的有效相关子查询吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！