I know that I should test my SQL script as well, but I don't have time to make a test before leaving for work! I can make a SQL Fiddle later, but for now it seems that no one has quite answered the question correctly.
SELECT COALESCE(t1.cod, t2.cod) cod, t1.text1, t2.text2
FROM t1 FULL OUTER JOIN t2 ON t1.cod = t2.cod
ORDER BY cod;
If you want to test the performance, use the link to the SQL Fiddle below!
So, a commentor below asked why I used
COALESCE. It's because of the
FULL OUTER JOIN. In this case, you are asking the query to return rows
WHERE t1.cod = t2.cod, but the
FULL OUTER JOIN ensures that
NULL entries are returned whenever there is no match.
In these cases, you can't know if a particular
cod will be in table
t2, or in both, so by using
COALESCE you ensure that so long as a one of the tables has the field
cod (which is guaranteed, since you did a
FULL OUTER JOIN), this will be filled in.
WARNING: One of the alternative posted responses is an incorrect solution.
Applying that erroneous query
select t1.cod, t1.text1, t2.text2 from t1 full outer join t2 on t1.cod=t2.cod ORDER BY cod;
NULLs in the
cod fields if the
cod was only present in table
See this SQL Fiddle for confirmation of the error.
However, applying my query with
COALESCE will yield the desired results.
Use this SQL Fiddle, to confirm.