CODEWITHSUNDEEP
sqliteinner-join
madmax1
madmax1

Reputation: 267

sqlite3 inner join comparing all columns

I have a sqlite database which contains two tables t1, t2 with the same schema. The tables consist multiple columns c1, c2, .., cn (more than 50). I am trying to find the entires that are present and identical in both tables. The issue I encounter is to compare all columns at once. Obviously, I don't want to compare every column explicitly. An example for two tables:

SELECT t1.*
FROM t1
INNER JOIN t2
ON t1.c1 = t2.c1
AND t1.c2 = t2.c2
...
AND t1.cn = t2.cn

I could generate this huge query using python's sqlite3 module i guess, but i wonder if there is a more elegant way.

Thanks

Upvotes: 1

Views: 429

Answers (2)

tvm
tvm

Reputation: 3449

Sqlite should support NATURAL JOIN, which joins by all "matching" columns by default.

SELECT
t1.*
FROM t1
NATURAL JOIN t2

Upvotes: 3

CL.
CL.

Reputation: 180020

In a compound SELECT, all columns in the subqueries are compared:

SELECT * FROM t1
INTERSECT
SELECT * FROM t2;

Upvotes: 2

Related Questions