时间戳

首页 > 解决方案 > 子查询如何引用它之外的表？

sql - 子查询如何引用它之外的表？

问题描述

我试图了解 JOIN 中的子查询如何引用上层查询中的字段。

车辆表存储公司使用的车辆的当前信息；所有的车辆历史都存储在名为 vehicle_aud 的表中，其结构与车辆表完全相同，但还包括对另一个表的引用，称为修订，该表存储有关谁、何时、为什么等对主表。

为了完成对车辆的最后一个操作，使用了一个非常简单的 JOIN，如下所示：

SELECT *
FROM vehicles v
    JOIN vehicles_aud vu ON vu.id=v.id AND vu.revision_id=(
        SELECT max(revision_id)
        from vehicles_aud
        WHERE id=v.id
    )
    JOIN revisions r ON r.id=vu.revision_id

请不要介意 SELECT 部分中的星号：我确定在这里指定任何真实字段对于我下面的问题没有多大意义。准确地说，这个查询也可以重写为以下方式以便更好地理解：

SELECT *
FROM vehicles v
    CROSS APPLY (
        SELECT TOP 1 *
        FROM vehicles_aud
        WHERE id=v.id
        ORDER BY id DESC
    ) vu
    JOIN revisions r ON r.id=vu.revision_id

在第二个示例中，JOIN 不适用。

我假设第一个示例中的子查询应与 CROSS APPLY 运算符一起使用，因为它引用了子查询之外的车辆表中的 id 字段，但 IRL 与上述 JOIN 的查询效果很好。我怀疑如果没有 CROSS APPLY，这怎么可能？我的意思是，在什么情况下，子查询如何引用子查询之外的表的字段？

标签： sqltsql

解决方案

我不确定，如果这真的能回答你的问题......

简而言之：任何类型的 JOIN 都会创建两个结果集并将它们与给定条件匹配，而任何类型的 APPLY 都会调用操作row-by-row。如果 APPLY 返回多行，则添加一个结果集（类似于 JOIN），而对于单行结果，引擎只需添加列。

现实会复杂得多。

该引擎非常智能，会在检查统计数据、索引、现有计划等后决定最佳计划。你得到的真正计划很可能不是你所期望的。对于看似不同的查询，您获得的计划很可能是相同的。

在启用“包括实际计划”的情况下尝试以下操作：

USE master;
GO
CREATE DATABASE testPlan;
GO
USE testPlan;
GO

CREATE TABLE t1 (ID INT IDENTITY CONSTRAINT pk PRIMARY KEY, SomeValue VARCHAR(100));
INSERT INTO t1 VALUES('MaxVal will be 100'),('MaxVal will be 200'),('MaxVal will be 300');
GO

CREATE TABLE t2(fkID INT CONSTRAINT fk FOREIGN KEY REFERENCES t1(ID),TheValue INT);
INSERT INTO t2 VALUES(1,1),(1,2),(1,100)
                    ,(2,1),(2,2),(2,200)
                    ,(3,1),(3,2),(3,300);
GO

--a scalar computation using MAX()
SELECT *
      ,(SELECT MAX(t2.TheValue) FROM t2 WHERE t1.ID=t2.fkID) AS MaxVal
FROM t1

--the same as above, but with APPLY
SELECT *
FROM t1
CROSS APPLY(SELECT MAX(t2.TheValue) FROM t2 WHERE t1.ID=t2.fkID) A(MaxVal)

--Now we pick the TOP 1 after an ORDER BY
SELECT *
      ,(SELECT TOP 1 t2.TheValue FROM t2 WHERE t1.ID=t2.fkID ORDER BY t2.TheValue DESC) AS MaxVal
FROM t1

--and again the same with APPLY
SELECT *
FROM t1
CROSS APPLY(SELECT TOP 1 t2.TheValue FROM t2 WHERE t1.ID=t2.fkID ORDER BY t2.TheValue DESC) A(MaxVal)

--Tim's approach using the very slick TOP 1 WITH TIES approach
SELECT TOP 1 WITH TIES *
FROM t1 INNER JOIN t2 ON t1.ID=t2.fkID
ORDER BY ROW_NUMBER() OVER(PARTITION BY t1.ID ORDER BY t2.TheValue DESC);

GO
USE master;
GO
--carefull with real data!
--DROP DATABASE testPlan;
GO

“标量 MAX”的计划使用 27（！）行的表扫描，减少到 9。与 APPLY 相同的方法具有相同的计划。引擎足够聪明，可以看到，这不需要完整的结果集。附带说明：您可以在查询中像变量一样使用 MaxVal，非常有帮助...

TOP 1在这个小测试中，带有子查询的计划是最昂贵的。它以与上面相同的方式开始（27 行的表扫描，减少到 9 行），但必须添加一个排序操作。APPLY 的变化大致相同。

with 的方法TOP 1 WITH TIES需要 9 行 t2 并对它们进行排序。以下操作针对 9 行进行。再进行一次排序并减少到 TOP 行。

在这种情况下，第一个是最快的 - 到目前为止。

但在（您的）现实中，实际行为将取决于现有索引、统计信息和实际行数。此外，您之间还有一个额外的级别（多一张桌子）。查询越复杂，优化器就越难找到最佳计划。

结论

如果性能很重要，那就赛马并进行测量。如果性能不是那么重要，请选择更易于阅读、理解和维护的查询。

推荐阅读