SQL Oracle fusion lines with same id-CodePudding

Here is a simplification of my problem:

SELECT 123 id, 1 toto , NULL titi FROM dual
UNION
SELECT 123 id, null toto , 2 titi FROM dual
->
ID |TOTO|TITI|
--- ---- ---- 
123|   1|    |
123|    |   2|

I would like to have a result like this (keeping those 2 selects):

ID |TOTO|TITI|
--- ---- ---- 
123|   1|  2 |

Not sure how to fusion those 2 lines

One ugly solution is:

SELECT id, min (toto), min(titi)
FROM (
SELECT 123 id, 1 toto , NULL titi FROM dual
UNION
SELECT 123 id, null toto , 2 titi FROM dual
)
GROUP BY id

CodePudding user response：

SELECT id, min (toto), min(titi)
FROM (
SELECT 123 id, 1 toto , NULL titi FROM dual
UNION
SELECT 123 id, null toto , 2 titi FROM dual
)
GROUP BY id

CodePudding user response：

For me it looks like a join with nvl for me:

with
 t1 as (SELECT 123 id, 1 toto , NULL titi FROM dual)
,t2 as (SELECT 123 id, null toto , 2 titi FROM dual)
select
   nvl(t1.id  , t2.id   ) id
  ,nvl(t1.toto, t2.toto ) toto
  ,nvl(t1.titi, t2.titi ) titi
from t1 full outer join t2
  on t1.id=t2.id

I'm not sure about your data, so probably it should be just left/right join or even inner join.