能否在SQL SELECT语句中将表用作列?

首先,我不认为您所介绍的代码实际有效:

SELECT
    column1, column2,
    (
      SELECT columnA, columnB
      FROM tableA
      JOIN tableB ON tableA.table_b_fk = tableB.my_pk
      FETCH FIRST 1 ROWS ONLY
    ) AS column3
FROM tableC

基本上与以下内容相同:

SELECT 1 AS c
   , (SELECT 'a', 'b' FROM dual)
FROM dual
-- and will yield
-- ORA-00913: too many values

您看到的构造是 标量子查询 在选择列表中(它本身不是一个表)。它必须返回一行和一个值(因此 FETCH FIRST 1 ROWS ONLY )。

SELECT 1 AS c
   , (SELECT 'a' FROM dual)  -- one column and one value subquery
FROM dual

如果要在外部查询中每行返回多个列,可以使用 CROSS/OUTER APPLY :

SELECT column1, column2, s.*
FROM tableC
OUTER APPLY (SELECT columnA, columnB
             FROM tableA
             JOIN tableB ON tableA.table_b_fk = tableB.my_pk
             FETCH FIRST 1 ROWS ONLY) s

仍然要使事情正确,它应该是相关的子查询。更多信息:

• Scalar Subquery Expressions
• Using Subqueries