1

I have one table1 like this:

col1, col2, col3
A,    B,    4
C,    B,    5

And another table2 like this: 

col1, col2, col4
A,    B,    2
C,    B,    1
C,    C,    3

I want the result after the join to be: 

col1, col2, col3,  col4
A,    B,     4       2
C,    B,     5       1
C,    C,     NaN     3

I tried a left outer join like this but it just eliminates that last tuple because C, C doesn't appear in the first table:

drop table if exists merge;    
create table merge as 
select * from table2 s2 left outer join table1 s1 on 'col2' and 'col1';

How do I get the desired result from a join operation? Thank you.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
makansij
  • 9,303
  • 37
  • 105
  • 183
  • 1
    You need to simulate a `FULL OUTER JOIN` not available in sqlite. Have a look [here](http://stackoverflow.com/questions/1923259/full-outer-join-with-sqlite) on how to do this. – Giorgos Betsos Dec 09 '15 at 07:45

2 Answers2

0

You should do a FULL OUTER JOIN. However, this is not available in SQLite, so you need to do a workaround and do a union of two left outer joins, like in this answer: https://stackoverflow.com/a/1923306/2947592

Community
  • 1
  • 1
wvdz
  • 16,251
  • 4
  • 53
  • 90
0

First, get the set of all possible IDs by concatenating them from both tables. Then you can do a left join with both the original tables:

SELECT col1, col2, col3, col4
FROM (SELECT col1, col2 FROM table1
      UNION
      SELECT col1, col2 FROM table2)
LEFT JOIN table1 USING (col1, col2)
LEFT JOIN table2 USING (col1, col2);
CL.
  • 173,858
  • 17
  • 217
  • 259