antonpug
antonpug

Reputation: 14296

SQL query to join results from two tables but also include rows that do not have counterparts in the other table?

Given two tables APPLE and ORANGE,

NAME      APPLES
Alice     5
Bob       10
Trudy     1

NAME      ORANGES
Bob       50
Trudy     10
Dick      10

How can I write a JOIN to show the table:

NAME      APPLES      ORANGES
Alice     5           -
Bob       10          50
Trudy     1           10
Dick      -           10

I currently have

SELECT a.NAME, APPLES, ORANGES
FROM APPLE a
JOIN
ORANGE o ON o.NAME = a.NAME

but that only returns the fields that have a value in both APPLE and ORANGE.

Upvotes: 4

Views: 1337

Answers (5)

Francis P
Francis P

Reputation: 13665

should be:

SELECT COALESCE(a.NAME,o.name) as Name, APPLES, ORANGES 
FROM APPLE a 
FULL OUTER JOIN ORANGE o ON o.NAME = a.NAME 

Example: http://sqlfiddle.com/#!4/1ae9a/4

Upvotes: 3

Thomas Tik
Thomas Tik

Reputation: 19

you have to use a left right outer join depending on which table contains the inclomplete data

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 51000

Change JOIN to FULL OUTER JOIN.

Upvotes: 1

Teja
Teja

Reputation: 13534

SELECT a.NAME, a.APPLES, o.ORANGES
FROM APPLE a
FULL OUTER JOIN
ORANGE o ON o.NAME = a.NAME

Upvotes: 4

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171491

SELECT COALESCE(a.NAME, b.NAME) as NAME, APPLES, ORANGES 
FROM APPLE a 
FULL OUTER JOIN ORANGE o ON o.NAME = a.NAME 

Upvotes: 7

Related Questions