I am refreshing my SQL.
I was reading about subqueries and the possibility to reference outside with correlated subqueries.
Example:
我正在刷新SQL。我正在阅读关于子查询的内容，以及在相关子查询之外引用的可能性。例子:
SELECT *
FROM ORDERS O
WHERE 'ROAD BIKE' =
(SELECT DESCRIPTION FROM PART P WHERE P.PARTNUM = O.PARTNUM)
This is equivalent with a join:
这等价于连接:
SELECT O.ORDEREDON, O.NAME,
O.PARTNUM, O.QUANTITY, O.REMARKS
FROM ORDERS O, PART P
WHERE P.PARTNUM = O.PARTNUM AND P.DESCRIPTION = 'ROAD BIKE'
My problem is that I didn't get the first form and when/why we use it. When are outside referenced queries useful?
我的问题是我没有得到第一种形式，以及我们为什么使用它。何时外部引用查询有用?
3 个解决方案
#1
2
Orders have a reference to the part number, so the Orders table has a foreign key to the part numbers.
订单具有对零件编号的引用，因此订单表具有零件编号的外键。
We want all the Orders where the part number is for "Road Bike".
我们要所有的订单，零件号是“公路自行车”。
The first form first does a sub-query on every record, to check if O.PARTNUM is a part number for "Road Bike".
第一个表单首先对每个记录执行子查询，以检查是否有O。PARTNUM是"Road Bike"的零件号。
The way to think of it is, the main query is going through every record in the Orders table. On each record, it does a sub query, where it's PARTNUM field is used in the query. So, if you use the Orders record's PARTNUM in the sub-query, select to find the record in the PART table with that PARTNUM, and select the DESCRIPTION field. Then the where clause of the main query is check if "Road Bike" equals the DESCRIPTION returned from the sub-query.
可以这样想，主查询将遍历Orders表中的每条记录。在每个记录上，它都执行子查询，查询中使用的是PARTNUM字段。因此，如果您在子查询中使用Orders记录的PARTNUM，那么选择在PART表中找到与这个PARTNUM对应的记录，然后选择DESCRIPTION字段。然后主查询的where子句是检查“Road Bike”是否等于子查询返回的描述。
I would recommend against using the first form, as it is a correlated query, and you should avoid correlated queries for performance reasons, so use the second form. A better version of the first form is:
我建议不要使用第一个表单，因为它是一个相关查询，由于性能原因，您应该避免相关查询，所以使用第二个表单。较好的第一种形式是:
SELECT *
FROM ORDERS O
WHERE O.PARTNUM =
(SELECT P.PARTNUM FROM PART P WHERE DESCRIPTION = 'ROAD BIKE')
This is not a correlated query. The database can do the subquery once, get the PARTNUM for the record with "ROAD BIKE" as the DESCRIPTION, and then run the main query with the condition WHERE O.PARTNUM equals the result of the sub-query.
这不是一个相关查询。数据库可以一次执行子查询，以“ROAD BIKE”作为描述，获取该记录的PARTNUM，然后运行主查询，条件是O。PARTNUM等于子查询的结果。
#2
2
In short, you should avoid correlated subqueries like the plague.
简而言之，您应该避免相关的子查询，如鼠疫。
Correlated subqueries execute the inner query once for every row in the outer table. This results in terrible performance (a 1 million row outer table will result in the inner query executing 1 million times!)
相关子查询对外部表中的每一行执行内部查询。这将导致糟糕的性能(100万行外表将导致内部查询执行100万次!)
A join on the other hand is quite efficient and databases are very good at optimising them.
另一方面，连接是非常高效的，而且数据库非常善于优化它们。
If possible, always express your query as a join in preference to a correlated subquery.
如果可能的话，始终将查询表示为优先于相关子查询的连接。
#3
0
A scenario where a subquery might be appropriate is something like this:
一个子查询可能合适的场景是这样的:
select some fields
from some tables
where some conditions are met
and somefield = (select min(something) from etc)
However, I don't know if that's a correlated subquery. Semantics aren't my strong point.
但是，我不知道这是否是一个相关的子查询。语义不是我的强项。
#1
2
Orders have a reference to the part number, so the Orders table has a foreign key to the part numbers.
订单具有对零件编号的引用，因此订单表具有零件编号的外键。
We want all the Orders where the part number is for "Road Bike".
我们要所有的订单，零件号是“公路自行车”。
The first form first does a sub-query on every record, to check if O.PARTNUM is a part number for "Road Bike".
第一个表单首先对每个记录执行子查询，以检查是否有O。PARTNUM是"Road Bike"的零件号。
The way to think of it is, the main query is going through every record in the Orders table. On each record, it does a sub query, where it's PARTNUM field is used in the query. So, if you use the Orders record's PARTNUM in the sub-query, select to find the record in the PART table with that PARTNUM, and select the DESCRIPTION field. Then the where clause of the main query is check if "Road Bike" equals the DESCRIPTION returned from the sub-query.
可以这样想，主查询将遍历Orders表中的每条记录。在每个记录上，它都执行子查询，查询中使用的是PARTNUM字段。因此，如果您在子查询中使用Orders记录的PARTNUM，那么选择在PART表中找到与这个PARTNUM对应的记录，然后选择DESCRIPTION字段。然后主查询的where子句是检查“Road Bike”是否等于子查询返回的描述。
I would recommend against using the first form, as it is a correlated query, and you should avoid correlated queries for performance reasons, so use the second form. A better version of the first form is:
我建议不要使用第一个表单，因为它是一个相关查询，由于性能原因，您应该避免相关查询，所以使用第二个表单。较好的第一种形式是:
SELECT *
FROM ORDERS O
WHERE O.PARTNUM =
(SELECT P.PARTNUM FROM PART P WHERE DESCRIPTION = 'ROAD BIKE')
This is not a correlated query. The database can do the subquery once, get the PARTNUM for the record with "ROAD BIKE" as the DESCRIPTION, and then run the main query with the condition WHERE O.PARTNUM equals the result of the sub-query.
这不是一个相关查询。数据库可以一次执行子查询，以“ROAD BIKE”作为描述，获取该记录的PARTNUM，然后运行主查询，条件是O。PARTNUM等于子查询的结果。
#2
2
In short, you should avoid correlated subqueries like the plague.
简而言之，您应该避免相关的子查询，如鼠疫。
Correlated subqueries execute the inner query once for every row in the outer table. This results in terrible performance (a 1 million row outer table will result in the inner query executing 1 million times!)
相关子查询对外部表中的每一行执行内部查询。这将导致糟糕的性能(100万行外表将导致内部查询执行100万次!)
A join on the other hand is quite efficient and databases are very good at optimising them.
另一方面，连接是非常高效的，而且数据库非常善于优化它们。
If possible, always express your query as a join in preference to a correlated subquery.
如果可能的话，始终将查询表示为优先于相关子查询的连接。
#3
0
A scenario where a subquery might be appropriate is something like this:
一个子查询可能合适的场景是这样的:
select some fields
from some tables
where some conditions are met
and somefield = (select min(something) from etc)
However, I don't know if that's a correlated subquery. Semantics aren't my strong point.
但是，我不知道这是否是一个相关的子查询。语义不是我的强项。