How can I make an SQL query that handles missing records?

Question

I've got two tables, which contain customer ages and heights.

Table: Ages
+-----------+------------+
|customerId |     age    |
+-----------+------------+
|     1     |     15     |
|     2     |     24     |
|     3     |     21     |
|     4     |     62     |
|     6     |     57     |
|     7     |     32     |
+-----------+------------+

Table: Heights
+-----------+------------+
|customerId |   height   |
+-----------+------------+
|     1     |     175    |
|     2     |     182    |
|     4     |     180    |
|     5     |     171    |
|     6     |     165    |
|     7     |     182    |
+-----------+------------+

I need to write a SELECT query that reads all the ages and heights. So something like this...

SELECT Ages.age, Heights.height 
FROM Ages INNER JOIN Heights ON Ages.customerId=Heights.customerId;

However (and here's the twist) due to sloppy record-keeping, there are missing records from both tables. (e.g. customerId 5 in Ages, and customerId 3 in Heights).

Is there a way to write the query so that it will still work, but return zeros whenever the data is missing?

i.e.

+-----------+------------+------------+
|customerId |     age    |   height   |
+-----------+------------+------------+
|     1     |     15     |     175    |
|     2     |     24     |     182    |
|     3     |     21     |     0      |
|     4     |     62     |     180    |
|     5     |     0      |     171    |
|     6     |     57     |     165    |
|     7     |     32     |     182    |
+-----------+------------+------------+

score 4 · Accepted Answer · answered Mar 04 '14 at 15:56

4

One way to go (they are others, as always)

select customerId, max(age), max(height)
from 
 (
  select customerId, age, 0 as height from Ages
  UNION
  select customerId, 0 as age, height from heights
 ) s
group by customerId;

see SqlFiddle

answered Mar 04 '14 at 15:56

Raphaël Althaus

59,727
6
96
122

score 2 · Answer 2 · edited May 23 '17 at 12:20

MySql doesn't have full outer joins, but you can simulate one with a LEFT JOIN, followed by a RIGHT JOIN, combined with a UNION, which will combine + eliminate duplicates:

SELECT Ages.age, COALESCE(Heights.height, 0)
FROM Ages 
LEFT OUTER JOIN Heights ON Ages.customerId=Heights.customerId
UNION
SELECT COALESCE(Ages.age, 0), Heights.height 
FROM Ages 
RIGHT OUTER JOIN Heights ON Ages.customerId=Heights.customerId;

SqlFiddle Here

score 1 · Answer 3 · answered Mar 04 '14 at 15:56

What you really need is a full outer join, but MySQL doesn't support that. Instead, get all the customers in a subquery and use left outer join:

select c.customerid, coalesce(a.age, 0) as age, coalesce(h.height, 0) as height
from (select customerid from ages union
      select customerid from heights
     ) c left outer join
     ages a
     on a.customerid = c.customerid left outer join
     heights h
     on h.customerid = c.customerid;

Federico J. · Answer 4 · 2014-03-05T19:38:23.870

0

~~Use a LEFT JOIN with an IF condition is NULL~~

SELECT Ages.age, IF (Heights.height IS NULL, 0, Heights.height) AS height
FROM Ages
LEFT JOIN Heights ON Ages.customerId=Heights.customerId;

OK ok, so quickly answer... above only will give you 0 as height. Problem is get also ages, but for that, better get all customers ids, left join ages and heights

Best answer is accepted answer, I leave this here because I learnt MySQL COALESCE() function, XD

edited Mar 05 '14 at 19:38

answered Mar 04 '14 at 15:52

Federico J.

15,388
6
32
51

1

That would handle null heights, but what about null ages? – Digital Chris Mar 04 '14 at 15:54
3

Note that COALESCE conforms to the SQL standard. It's also neater. – Strawberry Mar 04 '14 at 15:55

How can I make an SQL query that handles missing records?

4 Answers4