CODEWITHSUNDEEP
sqlitesetinner-joinresultsetexists
Radio Controlled
Radio Controlled

Reputation: 950

SQLite: Inner join on result set

In SQLite, is there way to do an inner join of some table on a result set?

Kind of like this hypothetical query:

SELECT el1,el2 FROM pairs
INNER JOIN (<some_query> AS result) ON el1 IN result AND el2 IN result;

The redundant working query should be:

SELECT el1,el2 FROM pairs
WHERE el1 IN (<some_query>) AND el2 IN (<same_query>);

Where <some_query> is:

SELECT el FROM set1 INNER JOIN set2 ON set1.el=set2.el;

Obviously, the latter looks like it might be very expensive.

What would be the preferred query?

Upvotes: 1

Views: 416

Answers (1)

forpas
forpas

Reputation: 164099

The logic of some_query can be written more efficiently with INTERSECT:

SELECT el FROM set1
INTERSECT
SELECT el FROM set2

and if you use a CTE for some_query then for your requirement you can do this:

WITH cte AS (
  SELECT el FROM set1
  INTERSECT
  SELECT el FROM set2
)
SELECT el1, el2 
FROM pairs
WHERE el1 IN cte AND el2 IN cte

Upvotes: 1

Related Questions