Combine two tables in SQLite

Question

I have two tables, ta and tb:

ta:

key col1  
--------
k1 a 
k2 c 

tb:

key col2  
-------
k2 cc 
k3 ee 

They connected by "key". I want to know how can I get a table, tc, like:

key col1 col2  
-------------
k1 a  
k2 c cc 
k3  ee

Is there a easy method instead of inserting every record? They are one million records of tables so I need an effective way. Thanks!

Answer 1

Make a VIEW of the two tables. Write a SELECT ... JOIN statement that gives you the result you want, and then use that as the base for a VIEW.

Example:

CREATE VIEW
  database.viewname
AS
  SELECT
    ta.key, 
    ta.col1,
    tb.col2
  FROM
    ta
   LEFT JOIN
    tb
   USING(key)

Answer 2

For what you are looking for you will need to do a FULL OUTER JOIN to make sure you don't miss any keys. Once you have the query working you can think about just using it or creating a view.

You may need to work around limitations of the DB if FULL OUTER JOIN isn't implemented you can normally just UNION a left and right outer join to create your full.

Answer 3

Using a VIEW is the right way to go if you're looking for the data to reflect changes in the original tables.

If you do actually want the data to be copied into a new table, you'll need to do something like:

CREATE TABLE tc(key,col1,col2)

INSERT INTO tc (key,col1,col2)
SELECT ta.key, ta.col1, tb.col2
FROM ta FULL OUTER JOIN tb USING(key)

That will populate the new table with data from the old tables, but they'll be able to vary independently.