表达依赖于其他结果的联合

如果我很好地理解了这个问题，那么您的任务可以通过使用递归查询来解决。Oracle（请参阅和 SQLServer（2005 及更高版本）都支持它。当然，不同的 RDMS 供应商使用略有不同的语法。

你是说相交吗？考虑 relvarsCustomers和Orders一个客户有零个、一个或多个订单的对应关系（不过我不会将其称为层次结构）。要查找有订单的客户：

( Customers { customer_ID } ) INTERSECT ( Orders { customer_ID } ) 

我宁愿怀疑这不是你的意思，请修改你的问题以添加示例数据和预期结果。

我不确定你所说的 UNION 是什么意思。请解释！

如果您指的是“垂直”组合行集的 UNION 运算符，那么这可能会为您解决问题：

SELECT
   CASE X.Which WHEN 1 THEN A.Column1 ELSE B.Column1 END Column1,
   CASE X.Which WHEN 1 THEN A.Column2 ELSE B.Column2 END Column2,
   ...
FROM
   TableA A
   CROSS JOIN (
      SELECT 1
      UNION ALL SELECT 2
   ) X (Which)
   LEFT JOIN TableB B
      ON B.AId = A.Id
      AND X.Which = 2
WHERE
  X.Which = 1
  OR B.AId IS NOT NULL

这将对两个表执行一次扫描操作，而不是针对以下查询对表 A 执行至少两次扫描：

SELECT
   A.Column1,
   A.Column2,
   ...
FROM
   TableA A
UNION ALL
SELECT
   B.Column1,
   B.Column2,
   ...
FROM
   TableA A
   INNER JOIN TableB B
      ON B.AId = A.Id

现在，当您说 UNION 时，您可能只是指数学交集，在这种情况下，上面的最后一个 SELECT 将是您所需要的——一个简单的 JOIN 操作：

SELECT
   A.Whatever,
   B.Whatever,
   ...
FROM
   TableA A
   INNER JOIN TableB B
      ON B.AId = A.Id

更新

显然，某些数据库引擎具有不同的功能。例如，我下面的示例脚本中的最后两个查询（据报道）在 MySQL 中具有非常不同的执行计划，但它们在 SQL Server 中是相同的，SQL Server 通过更改连接顺序、左/右输入位置和移动来选择最佳访问路径根据需要周围的条件。它不会卡住先做 JOIN，然后再做 WHERE。

为了支持我关于 SQL Server 的声明，我编写了一些测试脚本。这会加载一个包含 100 万行的父表和一个包含大约 250 万行的子表。我们正在寻找的各个行都很好地放入堆栈中（完全没有必要，我知道，但是嘿，这很有趣）。

CREATE DATABASE Proof;
GO
ALTER DATABASE Proof SET RECOVERY SIMPLE --no need to bloat the tran log
USE Proof;
GO
CREATE TABLE books (
   id int identity(1,1) NOT NULL CONSTRAINT PK_books PRIMARY KEY CLUSTERED,
   title varchar(100)
);

CREATE TABLE characters (
   book_id int not null constraint fk_characters foreign key references books (id),
   name varchar(100),
   CONSTRAINT PK_characters PRIMARY KEY CLUSTERED (book_id, name)
);

SET NOCOUNT ON;
DECLARE
   @book int,
   @rowcount int,
   @lastbookid int,
   @which int;

SET @book = Coalesce((SELECT Count(*) FROM books), 0);
SET @which = 1;
WHILE 1 = 1 BEGIN
   INSERT books
   SELECT Left(Replicate('-' + Convert(varchar(11), @book + v.number), 20), 100)
   FROM master.dbo.spt_values v
   WHERE   
      v.type = 'P'
      AND v.number < 1000000 - @book;

   SELECT @rowcount = @@rowcount, @lastbookid = scope_identity();
   IF @rowcount = 0 BREAK;
   SET @book = @book + @rowcount;

   INSERT characters
   SELECT
      B.id, Left(Replicate('|' + Convert(varchar(11), v.number), 20), 100)
   FROM
      books B
      CROSS JOIN master.dbo.spt_values v
   WHERE
      B.id BETWEEN @lastbookid - @rowcount + 1 AND @lastbookid
      AND v.type = 'P'
      AND v.number BETWEEN 1 AND Convert(int, Rand() * 4) + 1;


   IF @book >= 250000 AND @which = 1 BEGIN -- put them deep inside
      INSERT books VALUES ('The Frog and the Sorcerer');
      INSERT characters
      SELECT scope_identity(), name
      FROM (
          SELECT 'Frog' UNION ALL SELECT 'Sorcerer'
      ) x (name);
      SET @book = @book + 1;
      SET @which = @which + 1;
   END
   ELSE IF @book >= 500000 AND @which = 2 BEGIN
      INSERT books VALUES ('The Princess and the Pea');
      INSERT characters
      SELECT scope_identity(), name
      FROM (
          SELECT 'Princess' UNION ALL SELECT 'Pea'
      ) x (name);
      SET @book = @book + 1;
      SET @which = @which + 1;
   END
   ELSE IF @book >= 750000 AND @which = 3 BEGIN
      INSERT books VALUES ('Two Ways to Tango');
      INSERT characters
      SELECT scope_identity(), name
      FROM (
          SELECT 'Tango Alpha' UNION ALL SELECT 'Tango Omega'
      ) x (name);
      SET @book = @book + 1;
      SET @which = @which + 1;
   END;
END;
GO
SET SHOWPLAN_ALL ON;
GO
SELECT A.title,B.name
FROM
    books A
    LEFT JOIN characters B
        ON A.id = B.book_id
WHERE
    A.title IN ('Two Ways to Tango', 'The Frog and the Sorcerer')
OPTION (MAXDOP 1);
GO
SET SHOWPLAN_ALL OFF;
GO
SET SHOWPLAN_ALL ON;
GO
SELECT A.title, B.name
FROM
    (
        SELECT id, title FROM books A
        WHERE title IN ('Two Ways to Tango', 'The Frog and the Sorcerer')
    ) A
    LEFT JOIN characters B
        ON A.id = B.book_id
OPTION (MAXDOP 1);
GO
SET SHOWPLAN_ALL OFF;
GO
USE master;
GO
DROP DATABASE Proof;

这两个执行计划是相同的。我抑制了并行性，因为它只是不需要的噪音（计划仍然相同）。这是删除查询后的 SHOWPLAN 结果（唯一不同的部分）。

StmtText                                                                                                                                                                                                                               StmtId NodeId Parent PhysicalOp           LogicalOp            Argument                                                                                                                                                                                               DefinedValues         EstimateRows EstimateIO EstimateCPU  AvgRowSize TotalSubtreeCost OutputList              Warnings Type     Parallel EstimateExecutions
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------ ------ -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------- ------------ ---------- ------------ ---------- ---------------- ----------------------- -------- -------- -------- ------------------
                                                                                                                                                                                                                                       1      1      0      NULL                 NULL                 1                                                                                                                                                                                                      NULL                  2.994377     NULL       NULL         NULL       12.71991         NULL                    NULL     SELECT   0        1
  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([A].[id]))                                                                                                                                                                        1      2      1      Nested Loops         Left Outer Join      OUTER REFERENCES:([A].[id])                                                                                                                                                                            NULL                  2.994377     0          0.0000125165 151        12.71991         [A].[title], [B].[name] NULL     PLAN_ROW 0        1
       |--Clustered Index Scan(OBJECT:([Proof].[dbo].[books].[PK_books] AS [A]), WHERE:([Proof].[dbo].[books].[title] as [A].[title]='The Frog and the Sorcerer' OR [Proof].[dbo].[books].[title] as [A].[title]='Two Ways to Tango')) 1      3      2      Clustered Index Scan Clustered Index Scan OBJECT:([Proof].[dbo].[books].[PK_books] AS [A]), WHERE:([Proof].[dbo].[books].[title] as [A].[title]='The Frog and the Sorcerer' OR [Proof].[dbo].[books].[title] as [A].[title]='Two Ways to Tango') [A].[id], [A].[title] 1            10.73646   1.100157     114        11.83662         [A].[id], [A].[title]   NULL     PLAN_ROW 0        1
       |--Clustered Index Seek(OBJECT:([Proof].[dbo].[characters].[PK_characters] AS [B]), SEEK:([B].[book_id]=[Proof].[dbo].[books].[id] as [A].[id]) ORDERED FORWARD)                                                                1      4      2      Clustered Index Seek Clustered Index Seek OBJECT:([Proof].[dbo].[characters].[PK_characters] AS [B]), SEEK:([B].[book_id]=[Proof].[dbo].[books].[id] as [A].[id]) ORDERED FORWARD                                                                [B].[name]            2.994377     0.003125   0.0001602938 50         0.003285294      [B].[name]              NULL     PLAN_ROW 0        0