FULL OUTER JOIN with two subselects gives "ORA-00918: column ambiguously defined"

Question

both Subselects runs fine standalone.

But I wish to join them togehter, which always gives "ORA-00918: column ambiguously defined". I don't see why?

Both subelects uses other names.

SELECT *
  FROM    (SELECT aods.user_id,
                  aods.firstname,
                  aods.lastname,
                  Aods.DEPARTMENT,
                  cods.jc_name
             FROM    personas aods
                  LEFT JOIN
                     user_jc cods
                  ON aods.user_id = cods.user_id
            WHERE cods.jc_name LIKE '%ADM%') ods
       FULL OUTER JOIN
          (SELECT abl.user_id,
                  abl.firstname,
                  abl.lastname,
                  Abl.DEPARTMENT,
                  bbl.ug_name
             FROM    personas abl
                  LEFT JOIN
                     ru_ug bbl
                  ON abl.user_id = bbl.user_id
            WHERE Bbl.UG_NAME LIKE '%ADM%'
                  AND bbl.rss_name = 'TIR') bl
       ON ods.user_id = bl.user_id

ramsesoriginal · Accepted Answer · 2012-02-06T11:47:25.910

Because you do a select * at the top.

user_id
firstname
lastname
DEPARTMENT

are present in both subqueries, and with select *it would get two columns with the same name.

If you want to merge the two subqueries (such that the resulting set of columns would be user_id, firstname, lastname, DEPARTMENT, jc_name, ug_name) you should do a natural join:

SELECT *
  FROM (SELECT aods.user_id, aods.firstname, aods.lastname, aods.department,
               cods.jc_name
          FROM personas aods LEFT JOIN user_jc cods
               ON aods.user_id = cods.user_id
         WHERE cods.jc_name LIKE '%ADM%') ods
       NATURAL JOIN
       (SELECT abl.user_id, abl.firstname, abl.lastname, abl.department,
               bbl.ug_name
          FROM personas abl LEFT JOIN ru_ug bbl ON abl.user_id = bbl.user_id
         WHERE bbl.ug_name LIKE '%ADM%' AND bbl.rss_name = 'TIR') bl

Instead if you want all the different columns, you should explicitly list them:

 SELECT ods.user_id, ods.firstname, ods.lastname, ods.department, ods.jc_name,
        bl.user_id, bl.firstname, bl.lastname, bl.department, bl.ug_name
   FROM (SELECT aods.user_id, aods.firstname, aods.lastname, aods.department,
                cods.jc_name
           FROM personas aods LEFT JOIN user_jc cods
                ON aods.user_id = cods.user_id
          WHERE cods.jc_name LIKE '%ADM%') ods
        FULL OUTER JOIN
        (SELECT abl.user_id, abl.firstname, abl.lastname, abl.department,
                bbl.ug_name
           FROM personas abl LEFT JOIN ru_ug bbl ON abl.user_id = bbl.user_id
          WHERE bbl.ug_name LIKE '%ADM%' AND bbl.rss_name = 'TIR') bl
        ON ods.user_id = bl.user_id

In your comment you say that you want to use nvl() on each column with an outer join. For me it works fine:

SELECT NVL (ods.user_id, bl.user_id), NVL (ods.firstname, bl.firstname),
       NVL (ods.lastname, bl.lastname), NVL (ods.department, bl.department),
       ods.jc_name, bl.ug_name
  FROM (SELECT '1' user_id, 'ods2' firstname, NULL lastname,
               'ods3' department, 'ods4' jc_name
          FROM DUAL) ods
       FULL OUTER JOIN
       (SELECT '1' user_id, NULL firstname, 'bl2' lastname, 'bl3' department,
               'bl4' ug_name
          FROM DUAL) bl ON ods.user_id = bl.user_id

are you making some additional stuff, like order by?

thank you ramsesoriginal for you help. The natural join works fine, but this is not what I want. I have some problems formatting this here. The full outer join gives me again "ORA-00918: column ambiguously defined" ` SELECT NVL (ods.user_id, bl.user_id), NVL (ods.firstname, bl.firstname), NVL (ods.lastname, bl.lastname), NVL (ods.department, bl.department), ods.jc_name, bl.ug_name ` — Sybil, Feb 06 '12 at 11:33
I can't come up with a better explanation, but this one doesn't ring true for me. I've never had a problem with `SELECT *` handling duplicate columns names -- it should automatically alias one of the two. Perhaps it depends on the client being used and how it is processing the results of the query. As an example, `SELECT * FROM (select level l from dual connect by level <= 10) a full outer join (select level l from dual connect by level <=15) b on b.l=a.l;` works just fine for me in SQL Developer and SQLPlus. — Dave Costa, Feb 06 '12 at 13:40
@DaveCosta I see some differences on the handling of this case when using the Oracle Client, The Data Direct Driver or directly from SQL Navigator... In some cases it works for me too, in some others it won't... — ramsesoriginal, Feb 06 '12 at 14:23

score 0 · Answer 2 · answered Feb 06 '12 at 19:32

Specify column names:

SELECT ods.*, bl.*
  FROM    (SELECT aods.user_id,
                  aods.firstname,
                  aods.lastname,
                  Aods.DEPARTMENT,
                  cods.jc_name
             FROM    personas aods
                  LEFT JOIN
                     user_jc cods
                  ON aods.user_id = cods.user_id
            WHERE cods.jc_name LIKE '%ADM%') ods
       FULL OUTER JOIN
          (SELECT abl.user_id,
                  abl.firstname,
                  abl.lastname,
                  Abl.DEPARTMENT,
                  bbl.ug_name
             FROM    personas abl
                  LEFT JOIN
                     ru_ug bbl
                  ON abl.user_id = bbl.user_id
            WHERE Bbl.UG_NAME LIKE '%ADM%'
                  AND bbl.rss_name = 'TIR') bl
       ON ods.user_id = bl.user_id

Perhaps you want UNION as follows [or UNION ALL]:

  SELECT aods.user_id,
          aods.firstname,
          aods.lastname,
          Aods.DEPARTMENT,
          cods.jc_name
     FROM    personas aods
          LEFT JOIN
             user_jc cods
          ON aods.user_id = cods.user_id
    WHERE cods.jc_name LIKE '%ADM%'

UNION SELECT abl.user_id, abl.firstname, abl.lastname, Abl.DEPARTMENT, bbl.ug_name FROM personas abl LEFT JOIN ru_ug bbl ON abl.user_id = bbl.user_id WHERE Bbl.UG_NAME LIKE '%ADM%' AND bbl.rss_name = 'TIR'

FULL OUTER JOIN with two subselects gives "ORA-00918: column ambiguously defined"

2 Answers2