randy randy - 1 month ago 11
MySQL Question

SQL: full outer join (ambitious column name)

I have two table, t1 and t2.

-- t1
id name address
1 Tim A
2 Marta B

-- t2
id name address
1 Tim A
3 Katarina C


If I do t1 full outer join with t2

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id


However, the result has ambitious
id
,
name
,
address
.
How do I rename this so that I don't have duplicate column name?




Attempt:

SELECT name, address FROM

(SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id) as derived_table;





return: ERROR- duplicate column name "name".

Answer

Ditch the * in the SELECT list.

Specify the list of expressions to be returned. And qualify all column references with either the table name, or preferably, a shorter table alias.

And assign an alias to the expression and that will be the name of the column in the resultset.

Also, the query shown is not equivalent to a FULL OUTER JOIN.

If the goal is return all rows from t1, and to also return rows from t2 where a matching row doesn't exist in t1, I'd do something like this...

  SELECT t.id            AS t_id
       , t.name          AS t_name
       , t.addr          AS t_addr
    FROM t1 t
   UNION ALL
  SELECT s.id
       , s.name
       , s.addr
    FROM t2 s
    LEFT
    JOIN t1 r
      ON r.id = s.id
   WHERE r.id IS NULL
Comments