MySQL多表加入空值

Question

我有以下两个表:

Codes
Code  key1   key2
1     a      hh
2     a   
3     a   
4     a   


Orders
key1   key2  val1
a      aa    foo
h      hh    bar

我想得到

Desired output
code   key1   key2  val1
1      a      hh    bar 
2      a
3      a
4      a

我已经在订单的内部连接中尝试过了:

select 
      ordersa.key2, 
      code, 
      ordersb.date
   from 
      order ordersa
         right join codes
            on codes.key1 = orders.key1
         inner join orders ordersb
            on codes.key2 = ordersb.key2

但这会删除 key2 列中的所有空数据，我得到:

code   key1   key2   val1
1      a      hh     bar 

关于我如何实现这一点有什么建议吗？

Answer 1

SELECT
  c.Code,
  c.key1,
  IFNULL(c.key2, '') key2,
  IFNULL(o.val1, '') val1,
FROM Codes c
LEFT JOIN Orders o ON c.key2 = o.key2