Is there a generic workaround to express a derived column list in Oracle (and MySQL)?

Question

Many SQL databases support what the SQL standard calls a <derived column list>. Such databases include at least CUBRID, Derby, Firebird, HSQLDB, Postgres, SQL Server, and Sybase SQL Anywhere. A (simplified) extract from the SQL:2008 specification

7.6 <table reference>

Format
<table reference> ::=
    <table or query name> [ [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ] ]
  | <derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]

This means, I can express things like these (e.g. in Postgres, which is pretty standards-compliant)

-- Rename a <table or query name> to u(b)
with t(a) as (select 1)
select * from t as u(b)

-- Rename a <derived table> to u(b)
select * from (select 1) as u(b)

Now, according to the Oracle documentation, I can't rename columns using a <derived column list> specification. I could of course rename tables and columns separately, like this:

-- Rename a <table or query name> to u(b)
with t(a) as (select 1 from dual)
select u.a b from t u;

-- Rename a <derived table> to u(b)
select u.a b from (select 1 a from dual) u;

But this requires more knowledge about the derived table (actual column names) than the previous syntax. Also, the renamed columns would only be available after the projection (e.g. in the ORDER BY clause), not in any other clauses, including the projection itself.

Is there a more generic way to rename tables AND columns the way the SQL standard suggests, in Oracle (and also MySQL)? In particular, this could be useful for things like array unnesting, pivot/unpivot table renaming, inlining complex subqueries, renaming results from table functions, etc.

N.B: Please do not focus on the above examples too much. They're really just here to illustrate the problem. Real-world queries are much more complex, so I'm looking for a very general way to implement renaming to u(b)

NOTE: I'm still looking for a solution that works on a database like MySQL. A related question:
How to select an unaliased numeric literal from a sub-select

Even in your first example, you still need to know how many columns will be returned. eg: select * from (select 1,2) as u(b) won't work, you need u(b,c). The select * part doesn't get you off the hook really. So a bit confused why adding the aliases is problematic (unless maybe the inner select part is coming from a table function?). — tbone, Jan 02 '13 at 20:16
Yes, I need to know how many columns there are (and probably their types, too). But I don't need to know what the columns are called. A table function is another good use-case for a `` where I'd like to simply rename things using a `` — Lukas Eder, Jan 02 '13 at 21:23
H2 does support CTEs albeit only recursive ones (which I find really annoying btw). But you can fake a non-recursive CTE using `select null where false` in the recursion part so the accepted answer would work in H2 too. — , Jan 07 '13 at 19:12
@a_horse_with_no_name: You're right, I've somehow missed that. It's only documented in the ["advanced"](http://www.h2database.com/html/advanced.html?highlight=recursive&search=recursive#recursive_queries) section. However, I cannot use `WITH` in a subquery / derived table, so that makes its use quite limited, for H2. Nonetheless, I've updated my answer to show your solution — Lukas Eder, Jan 07 '13 at 20:31

Bill Karwin · Accepted Answer · 2013-01-07T22:15:59.340

10

For a MySQL solution, you could use a UNION to set the names of all the columns in a zero-row query term, and then subsequently query something more complex:

SELECT null AS a, null AS b, null AS c FROM dual WHERE false
UNION ALL
SELECT <expr>, <expr>, <expr>
FROM <realtable>...

Only the first query term of a UNION defines the column names of the whole query. Column names (or lack thereof) in subsequent query terms don't affect the ultimate column names.

You do need to know the number of columns, but it should be pretty easy to keep the two query terms separate. As far as I know, it works in both Oracle and MySQL (however, I have only tested it in MySQL, not in Oracle).

edited Jan 07 '13 at 22:15

answered Jan 07 '13 at 21:24

Bill Karwin

538,548
86
673
828

1

@LukasEder I don't think you're going to get anything better than that. – Conrad Frix Jan 07 '13 at 21:49
This works perfectly on H2, MySQL, Oracle, SQLite, all of which do not support derived column lists. In Oracle, I could use [tbone's solution](http://stackoverflow.com/a/14140167/521799) using common table expressions, but this one here probably has less negative impact on the query execution plan, as I suspect that the dummy query can be optimised away (whereas CTEs usually have some side-effects) – Lukas Eder Jan 07 '13 at 22:24
... oh yes, and knowing the *number* of columns is not a problem. I would need to know them when applying derived column lists, too: `u(b)`, or `u(b, c, d)`, etc... – Lukas Eder Jan 07 '13 at 22:52
Is there a way to preserve original nullability of columns? Using `UNION ALL` will made all of them as NULL. **[demo](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=70a103687873e9693b69aca50e2ddb8a)** And ETL tools do not like when metadata is not alligned. – Lukasz Szozda Jan 01 '19 at 12:18
@LukaszSzozda, I'm more familiar with MySQL, but the `CREATE TABLE...AS SELECT` syntax documentation shows that one can declare the column types and `NOT NULL` and other options before the `SELECT` query. https://dev.mysql.com/doc/refman/5.7/en/create-table-select.html But I haven't found any doc that shows Oracle can do the same. – Bill Karwin Jan 01 '19 at 16:58
@LukaszSzozda, That's to handle your example of `CREATE TABLE...AS SELECT`. If you're worried about result sets in general, I would think that the columns of result sets are always nullable. Otherwise we couldn't do things like `OUTER JOIN`. – Bill Karwin Jan 01 '19 at 17:09
@BillKarwin OK, I think that I didn't explain my intention clearly. `CREATE TABLE AS SELECT` was only to check column metadata. My point is that adding `SELECT null AS a, null AS b, null AS c FROM dual WHERE false` even if it does not add new rows it perfroms two things 1) change column names (anticipated) 2) mark every column of resultset as nullable (I would rather avoid it if possible) I will create another more descriptive demo. – Lukasz Szozda Jan 02 '19 at 15:37
Here is demo for SQL Server and using function for checking metadata **[demo](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=dd68f2bbf1dbd810b3b492e242644493)** Similar thing could be achieved with DBMS_SQL. In first comment I used CREATE TABLE only to materialize query to quickly get metadata. As for `OUTER JOIN` columns from `SELECT t1.*, t2.* FROM t1 [LEFT/RIGHT] JOIN t2` only columns from t2 are always nullable, t1 have theri original nullability **[demo2](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=dd8c80d67662d4d0b609593cfb04ee4a)** – Lukasz Szozda Jan 02 '19 at 15:43
I see. Would it fix the problem if we use non-NULL values in the first SELECT of the solution I gave? – Bill Karwin Jan 02 '19 at 16:37
@BillKarwin Yes, it will help [demo](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d6e028b150185f8ea7e336f7c532f93a). But then I will get even worse issues with implicit conversions and possible data type mismatch [demo2](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c977a46cb56ef6122d98b971cb7407e8) and casting to specific data type would nullify all the benefits of this solution. – Lukasz Szozda Jan 04 '19 at 20:59
@LukaszSzozda, Well, choose placeholder values with compatible data types. But honestly, I'm not sure why the nullability of columns of a result set is important. I've never even seen anyone ask that question. – Bill Karwin Jan 04 '19 at 21:08
@BillKarwin It is important for ETL tools/ORM frameworks. Converting to correct datatypes does not give any benefits becasue then it is simlpler to `SELECT cola AS a, colb AS b, ...` and the point of this question was to avoid it. – Lukasz Szozda Jan 04 '19 at 21:10

score 9 · Answer 2 · answered Jan 03 '13 at 13:47

Since you MUST know the number of columns, but not necessarily the column names, you can use the WITH clause to rename these columns as you wish. For example (WITH works in Oracle and SQL Server, don't have MySQL instance handy):

WITH t(x,y,z) as (select * from TABLE(fn_returning_xcols(3)))
select * from t;

Here we don't know the column names in the inner select, but we can rename them in outer WITH clause.

Another example using a PIVOT in Oracle:

WITH t(a,b,c,d,e) as 
(
 select * from 
 (
  select level as levl from dual connect by level <= 5
 )
 PIVOT(max(levl) as l for levl in (1,2,3,4,5))
)
select * from t;

Again, we don't care what the inner select column names are (the inner pivot creates somewhat odd column names), we just need to know how many columns and we can rename.

Nice thinking, I hadn't thought of CTE's (even if my own examples use them... duh). I checked with my Oracle 11g2 instance, where common table expressions are possible in views/subqueries as well. This would allow me to write (nasty) things like: `select * from (with u(b) as (select 1 from dual) select * from u)`. At least, it works for Oracle, which is better than nothing — Lukas Eder, Jan 03 '13 at 18:10

score 0 · Answer 3 · edited May 23 '17 at 12:25

As suggested by user tbone here, common table expressions are a good solution to my problem, at least for Oracle. For completeness, here is my example query written using CTEs in Oracle

-- Rename a <derived table> to u(b) with Oracle
with u(b) as (select 1 from dual) 
select u.b from u

-- Rename a <derived table> to u(b) with H2, which only knows recursive CTEs
-- Thanks to a comment by user a_horse_with_no_name
with recursive u(b) as (
  select 1
  union all
  select null where false
)
select u.b from u

Is there a generic workaround to express a derived column list in Oracle (and MySQL)?

3 Answers3

Linked