Without using aliases, how can I identify the merged columns from the inner query?
Due to some dynamic SQL complications, I need to join two tables together and query the result as if it were one table. The twist is that in the inner query I have to SELECT *
- I cannot use column aliases. There seems to be no way then to identify these 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 filter their unique columns quite easily
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 the 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 what join_key
is an invalid identifier. Too t1.join_key
, test_tab1.join_key
, "t1.JOIN_KEY"
, "t1.JOIN_KEY"
and "TEST_TAB1.JOIN_KEY"
. How to identify columns selected with an asterisk?
source to share
You don't need to use aliases, but you do need to be clearer about the columns in your view. 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 are using an inner join, there is no need to distinguish between two different join_key columns. You just need to specify one of them in the inner query so there is no confusion in the outer query.
source to share
Your queries are not valid SQL. The columns specified in the subquery must be unique, and * is just syntactic sugar for all columns. Even the one you said "easy enough" is not valid.
I guess I was wrong. Oracle does not seem to follow ANSI SQL and allows subqueries that can create ambiguous sets of columns. This was compounded by the fact that SQLFiddle requires inserts to be done on the DML side, which is why it was providing a false result.
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));
Request 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 fix this problem:
- Make sure all tables have unique columns (e.g. join_key1, join_key2).
- Explicitly list all dynamically generated alias columns that are guaranteed to be unique, for example tablename + columnname (i.e. t1.join_key AS test_tab1_join_key, t2.join_key AS test_tab2_join_key ...).
- Explicitly list a subset of columns by the second pair of join columns (i.e. t1.join_key, t1.tab1_uqcol, t2.tab2_uqcol).
- Don't use a generic subquery ( SQLFiddle example ).
All three have different drawbacks, so it depends on how you need to use this query. The second guarantees the ability to create reference sets of columns for all possible tables. The third method might be a little simpler, but it might fail if the two tables have the same column name for a non-join column.
In edition: I stand corrected. You're right. Oracle allows you to reference duplicate columns in a subquery. Even if Oracle allows you to do this, I will still avoid it as it is bad practice. It will also fail to produce correct SQL in most RDMSs.
I'll leave the answer, because unless someone finds an undocumented way to reference the duplicate column name from an outer query, you may have no choice but to change the inner query.
source to share