CODEWITHSUNDEEP
sqloracle-databasesubquery
Henry Yang
Henry Yang

Reputation: 2593

Can you use a table as a column in SQL SELECT statement?

I'm working with code from other people and see some use of Oracle SQL that I don't understand:

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

I have trouble understanding why they use this:

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

Did they just use a table as a column? I thought you can only use columns?

Can you really do that? How does it work?

I don't know where to find explanation of this usage so I ask here.

Please correct me if there is any misunderstanding!

Upvotes: 5

Views: 1767

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

First of all I don't believe that the code you presented actually works:

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

It is basically the same as:

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

The construct you see is a scalar subquery in SELECT list (it is not a table per se). It has to return one row and one value(thus you have FETCH FIRST 1 ROWS ONLY).

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

If you want to return more than one column per row in outer query you could use 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

Still to make thing right it should be correlated subquery. More info:

Upvotes: 3

Related Questions