SQL, Android SQLITE Join

Question

I have 2 tables which look like:

Table A
key1 key2 key3
1    a    a
2    a    a
3    a    a

Table B
key1 key2 key3
1    xxx  zzz
2    yyy  www

Now i want to have a big table out of A and B where A.key1 matches B.key1 the content of B is pasted otherwise if no key1 matches default values are pasted

for example

Key1 Key2 Key3 Key5 Key6
1    a    a    xxx  zzz            
2    a    a    yyy  www               
3    a    a    0    0       

What is the query?

Answer 1

you need LEFT JOIN:

select
  a.key1,
  a.key2,
  a.key3,
  ifnull(b.key2, 0) as key4,
  ifnull(b.key3, 3) as key5
from
  tableA a
left join
  tableB b on b.key1=a.key1

Answer 2

select * from a left outer join b on a.key1 = b.key1

(this will give nulls for the b columns where there is no matching row from b)