Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 289
antonpug Avatar asked Dec 07 '22 15:12

antonpug


2 Answers

SELECT COALESCE(a.NAME, b.NAME) as NAME, APPLES, ORANGES 
FROM APPLE a 
FULL OUTER JOIN ORANGE o ON o.NAME = a.NAME 
like image 70
D'Arcy Rittich Avatar answered Dec 22 '22 00:12

D'Arcy Rittich


SELECT a.NAME, a.APPLES, o.ORANGES
FROM APPLE a
FULL OUTER JOIN
ORANGE o ON o.NAME = a.NAME
like image 32
Teja Avatar answered Dec 21 '22 22:12

Teja