0

How do I join these two tables together?

type count
NULL 117
2    1

type count
NULL 807
1    3
2    32

I've tried INNER JOIN, LEFT JOIN, and RIGHT JOIN and I can't quite figure it out. I would like the end result to look like

type count count
NULL 117   807
1    NULL  3
2    1     32
allenylzhou
  • 1,431
  • 4
  • 19
  • 36

4 Answers4

1

You have a problem because NULLs do not match by default. But, you can make them match. Let me assume the first is t1 and the second t2

select t2.type, t1.count, t2.count
from t2 left join
     t1
     on t2.type = t1.type or (t2.type is null and t1.type is null);

Here is a SQL Fiddle that demonstrates that this correctly answers the OP's question.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • And if `t1` has a `type` that's not in `t2`? – Madbreaks Dec 09 '14 at 19:17
  • @Madbreaks . . . That doesn't appear to be this particular question. If you want to ask such a question, I suggest you do so as a question and not in a comment. – Gordon Linoff Dec 09 '14 at 19:20
  • Based on the nature of the op's question, I believe a complete answer would allow for such a scenario. – Madbreaks Dec 09 '14 at 19:38
  • You have a rather perverse interpretation of a question that starts "How do I join these two tables together?". In addition, this answer is correct. Your presumed downvote is counterproductive and hostile. – Gordon Linoff Dec 09 '14 at 19:46
  • Your definition of perverse is strange. But I agree about the downvote and tried to remove it, but it wouldn't allow me unless the answer is edited. If you care to make a trivial edit, I will remove it. – Madbreaks Dec 09 '14 at 19:54
  • @Madbreaks . . . I have no need need to edit the question except for adding the SQL Fiddle. It has been the correct answer since I wrote it. You should learn to be very careful with downvotes. I no longer downvote, because of mistakes I have made in the past. – Gordon Linoff Dec 09 '14 at 20:07
  • +One. A deserved upvote for the correct answer. The Fiddle is a nice tool. – Minnow Dec 09 '14 at 21:00
1
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

Sourced from this question

Community
  • 1
  • 1
Minnow
  • 1,733
  • 2
  • 26
  • 52
0

The right type of join would be FULL OUTER JOIN, but it does not exist in MySQL. We can emulate it in the following way:

SELECT t1.type, t1.count, t2.count  # exact matches
FROM t1 join t2 on t1.type = t2.type
UNION
select t1.type, t1.count, NULL      # t2 not present
FROM t1 left join t2 on t1.type = t2.type
WHERE t2.type IS NULL
UNION
select t2.type, NULL, t2.count      # t1 not present
FROM t2 left join t1 on t2.type = t1.type
WHERE t1.type IS NULL
UNION 
select NULL, t1.count, t2.count # NULL on both sides
FROM t1, t2
WHERE t1.type IS NULL and t2.type IS NULL;
Ashalynd
  • 12,363
  • 2
  • 34
  • 37
0

I have a question for the question ;)

What would happen if the tables would look like follows? (just a small change)

type count
NULL 117
3    333
2    1

type count
NULL 807
1    3
2    32

Because in this case both tables contain records that do not match the other table, so probably joining from one direction is not enough and you need to join the tables from both directions, but then, you might have trouble using data for the 'type' from only one table...

So one solution might be something like:

select if (t1.type is null, t2.type, t1.type) as type, t1.count count1, t2.count count2
  from t1
  left join t2 
    on t1.type=t2.type or (t1.type is NULL and t2.type is NULL)
union
select if (t1.type is null, t2.type, t1.type) as type, t1.count count1, t2.count count2
  from t1
  right join t2
    on t1.type=t2.type or (t1.type is NULL and t2.type is NULL);

In addition,

  • you may also use the coalesce() function instead of if (.. is null, ...) e.g. coalesce(t1.type, t2.type)
  • you may still need to be careful with union, perhaps you want to keep duplicated records (if there is any) and use union all

http://www.sqlfiddle.com/#!2/302e69/2

lp_
  • 1,158
  • 1
  • 14
  • 21