Menu
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.
503
To query data from multiple tables, you use INNER JOIN clause. The INNER JOIN clause combines columns from correlated tables. Suppose you have two tables: A and B. A has a1, a2, and f columns.
INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate.
SQLite supports different types of SQL Joins, like INNER JOIN, LEFT OUTER JOIN, and CROSS JOIN. Each type of JOIN is used for a different situation as we will see in this tutorial.
It is a kind of join that results in the combination of all rows from the first table with all rows in the second. In SQLite, the CROSS JOIN produced a result set which is the product of rows of two associated tables when no WHERE clause is used with CROSS JOIN.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
