Oracle SQL WITH clause select joined column

Question

SQL:

WITH joined AS (
    SELECT * 
    FROM table_a a 
    JOIN table_b b ON (a.a_id = b.a_id)
)
SELECT a_id 
FROM joined 

returns invalid identifier.

How can you select joined column when using WITH clause? I have tried aliases, prefixing and nothing worked. I know I can use:

WITH joined AS (
    SELECT a.a_id 
    FROM table_a a 
    JOIN table_b b ON (a.a_id = b.a_id)
)
SELECT a_id 
FROM joined

but I need this alias to cover all fields.

Only way I managed to meet this condition is using:

WITH joined AS (
    SELECT a.a_id a_id_alias, a.*, b.* 
    FROM table_a a 
    JOIN table_b b ON (a.a_id = b.a_id) 
)
SELECT a_id_alias 
FROM joined 

but it is not perfect solution...

Answer 1

You can use the effect of the USING clause when joining the tables.

When you join tables where the join columns have the same name (as it is the case with your example), the USING clause will return the join column only once, so the following works:

with joined as (
  select *
  from table_a a 
    join table_b b using (a_id)
)
select a_id
from joined;

SQLFiddle example: http://sqlfiddle.com/#!4/e7e099/2