1

I have a Oracle database with a table t1 with:

cod | text1
-----------
1   | aaa
2   | bbb
3   | ccc

table t2 with:

cod | text2
-----------
2   | abc
4   | mlc
5   | asc

how can I optain something like:

cod | text1 | text2
-------------------
1   | aaa   |
2   | bbb   | abc
3   | ccc   |
4   |       | mlc
5   |       | asc

If I try to do:

select * from t1
inner join t2 on t1.cod = t2.cod

doesn't works, it gives me only the intersection or something.

  • 2
    use FULL JOIN – ypercubeᵀᴹ Jul 8 '15 at 14:16
  • 1
    That would be a FULL OUTER JOIN, not INNER. – mustaccio Jul 8 '15 at 14:16
3

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!

SQL Fiddle

EDIT:

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 t1, 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;

yields NULLs in the cod fields if the cod was only present in table t2.

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.

  • I was trying something like test=# select cod from t1 union select t2.cod from t2 order by cod; How would I bring in the text with this construct? – Vérace Jul 8 '15 at 16:07
  • 1
    Why COALESCE(t1.cod, t2.cod) ? – miracle173 Jul 8 '15 at 17:51
  • Why use COALESCE? Because you have to if you want to return the cod values for all columns. In fact, the answer above mine, which has 2 upvotes, is actually incorrect! I''l show as much in a edit to my post. – Chris Jul 8 '15 at 22:08
2

select t1.cod, t1.text1, t2.text2 from t1 full outer join t2 on t1.cod=t2.cod

1

Use "full outer join". Your query should like,

select * from t1 full outer join t2 on t1.cod=t2.cod

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.