CODEWITHSUNDEEP
sqloracle-databaseouter-join
Thanthla
Thanthla

Reputation: 586

How to use the result of a full outer join correctly?

Assuming following simplified situation:

On a birthday party two games are provided (Darts and Bowling). The scores of each guest for each game are stored in separate tables:

Darts:                         Bowling:
Name  | Dart-Score             Name  | Bowling-Score
------------------             --------------------
Alice |  120                   Bob   | 25
Tim   |   90                   Alice |  8

Now I want to join the tables like this (ordered by Name):

Name  | Dart-Score | Bowling-Score
---------------------------------
Alice | 120        | 8
Bob   | NULL       | 25
Tim   | 90         | NULL

My approach was the following:

select *
from Darts d full outer join Bowling b on (d.Name=b.Name)
order by Name;

This obviously throws an error, since Name is ambiguous in the join table:

Darts.Name  | Dart-Score | Bowling.Name | Bowling-Score
-------------------------------------------------------
Alice       | 120        | Alice        | 8
Tim         | 90         | NULL         | NULL
NULL        | NULL       | Bob          | 25

Also, I cannot order along, e.g., Darts.Name, since some values are NULL. So, how can I order the result along the join attribute of a full outer join? Or is an outer join the wrong approach in this scenario?

My Solution:

Thanks to @jarlh and @Radu Gheorghiu I used the follwoing query:

select coalesce(d.Name,b.Name) Names, Dart-Score, Bowling-Score
from Darts d full outer join Bowling b on (d.Name=b.Name)
order by Names;

Upvotes: 1

Views: 54

Answers (3)

Vikram Jain
Vikram Jain

Reputation: 5588

Execute query as per below and Fiddle link:

-- First union all values and group by name in query:

create table Darts(id integer, Name  varchar(25), DartScore integer);
create table Bowling(id integer, Name  varchar(25), BowlingScore integer);

insert into Darts  values(1, 'Alice',120);
insert into Darts  values(1, 'Tim',90);

insert into Bowling  values(1, 'Bob',25);
insert into Bowling  values(1, 'Alice',8);


select name, DartScore, NULL as  BowlingScore from Darts
union all 
select name, NULL as DartScore,  BowlingScore from  Bowling;

select name, SUM(DartScore) as DartScore from Darts group by name;

select name, SUM(DartScore) as DartScore, SUM(BowlingScore) as BowlingScore 
from (
select name, DartScore, NULL as  BowlingScore from Darts
union all 
select name, NULL as DartScore,  BowlingScore from  Bowling
) 
group by name;

Upvotes: 0

Vash
Vash

Reputation: 1787

Alternatively, you can internally create an exhaustive list of names and left join with each of those two tables to create a common Name column in the output. 

select a.Name, d.Dart-Score, b.Bowling-Score
from 
(select name from Darts 
 UNION 
 select name from Bowling) a
LEFT JOIN
Darts d on a.Name = d.Name 
LEFT JOIN
Bowling b on a.Name = b.Name
ORDER BY a.Name;

ORDER BY can be applied to the UNIONED list.

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

  1. You can order by NVL(d.Name, b.Name) or COALESCE(d.Name, b.Name)

Since it's a full join, one of the columns will have a name. And by using NVL() you will always get a non-NULL result.

  1. The OUTER JOIN is the right approach in this scenario.

Upvotes: 1

Related Questions