Without using aliases, how can I identify joined columns from an inner query?

Question

Due to some dynamic SQL complications, I have a need to join two tables together and query from the result as if it were a single table. The twist is that I have to SELECT * on the inner query -- I can't alias the columns. There seems to then be no way to identify those columns in the outer query.

Take for example these tables

CREATE TABLE test_tab1 (join_key NUMBER, tab1_uqcol VARCHAR2(30));
CREATE TABLE test_tab2 (join_key NUMBER, tab2_uqcol VARCHAR2(30));
INSERT INTO test_tab1 VALUES (1, 'table 1 only');
INSERT INTO test_tab2 VALUES (1, 'table 2 only');

I can easily enough filter on their unique columns

SELECT *
FROM (
    SELECT *
    FROM test_tab1 t1
    INNER JOIN test_tab2 t2
    ON t1.join_key = t2.join_key
) joined
WHERE tab1_uqcol LIKE 'table%';

but if I try to refer to a column they have in common...

SELECT *
FROM (
    SELECT *
    FROM test_tab1 t1
    INNER JOIN test_tab2 t2
    ON t1.join_key = t2.join_key
) joined
WHERE join_key = 1;

then it tells me that join_key is an invalid identifier. Ditto t1.join_key, test_tab1.join_key, "t1.JOIN_KEY", "T1.JOIN_KEY", and "TEST_TAB1.JOIN_KEY". How do I identify these star-selected columns?

Answer 1

You don't need to use Aliases, but you do need to be more explicit with naming columns in your derived table. For example...

    SELECT *
    FROM (
        SELECT t1.join_key, t1.tab1_uqcol, t2.tab2_uqcol
        FROM test_tab1 t1
        INNER JOIN test_tab2 t2
        ON t1.join_key = t2.join_key
    ) joined
    WHERE join_key = 1;

Since you're using an inner join there is no need to differentiate between the two different join_key columns. You just need to list one of them in your inner query so that there is no confusion in the outside query.

Answer 2

Your queries are not valid SQL. The columns specified in a subquery must be unique and * is simply a syntactic sugar for all columns. Even the one you said is 'easy enough' is invalid.

It appears I was incorrect. Oracle does not seem to follow ANSI SQL and allows subqueries which can produce ambiguous column sets. This was compounded by the fact that SQLFiddle requires inserts to be done on the DML side so it provided a false positive.

SQL Fiddle

Schema Setup (Oracle 11g R2)

CREATE TABLE test_tab1 (join_key NUMBER, tab1_uqcol VARCHAR2(30));
CREATE TABLE test_tab2 (join_key NUMBER, tab2_uqcol VARCHAR2(30));

Query 1:

INSERT INTO test_tab1 VALUES (1, 'table 1 only');
INSERT INTO test_tab2 VALUES (1, 'table 2 only');

SELECT *
FROM (
    SELECT *
    FROM test_tab1 t1
    INNER JOIN test_tab2 t2
    ON t1.join_key = t2.join_key
) joined

Results:

ORA-00900: invalid SQL statement

There are three ways to modify your queries to resolve this:

1. Ensure all the tables have unique columns (i.e. join_key1, join_key2).
2. Explicitly list all columns with dynamically produced aliases guaranteed to be unique such as tablename + columnname (i.e. t1.join_key AS test_tab1_join_key, t2.join_key AS test_tab2_join_key ...).
3. Explicitly list a subset of columns by the second pair of join columns (i.e. t1.join_key, t1.tab1_uqcol, t2.tab2_uqcol).
4. Avoid using subquery all together (SQLFiddle example).

All three have different drawbacks so it depends on how you need to use this query. The second one is guaranteed to produce referenceable column sets for all possible tables. The third method may be a little easier but it may fail if two tables share the same column name for a column other than the joining columns.

On edit: I stand corrected. You are right. Oracle does allow you to reference duplicate columns in the subquery. Even if Oracle allows you to do it, I would still avoid it as it is bad practice. It also will not produce valid SQL in most RDMS.

I will leave the answer because unless someone finds an undocumented way to reference a duplicate column name from an outer query you may have no choice but to modify the inner query.