Join two tables in SQLite

Question

I have two tables: ta and tb:

that:

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

T.B .:

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

They are connected by a "key". I want to know how to get a table, tc, for example:

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

Is there an easy way to insert each record instead? This is a million table entries, so I need an efficient way.

Answer 1

Make VIEW of two tables. Write a SELECT ... JOIN that will give you the desired result, and then use it as a base for VIEW.

Example:

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

Answer 2

Using VIEW is the right way if you are looking for data to reflect changes in source tables.

If you really want the data to be copied to a new table, you 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) 

This will populate the new table with data from the old tables, but they can vary independently.

Answer 3

For what you are looking for, you will need a FULL EXTERNAL CONNECTION to make sure you don't miss a single key. After you work with a request, you can only think about using it or creating a view.

You may need to circumvent the restrictions in the database, if the FULL OUTER JOIN is not implemented, you can simply simply UNION create the left and right outer joins to create the full version.