SQLite: Inner join on result set

Question

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?

You could rewrite those `IN` subqueries using exists clauses, but other than that, the second query is a perfectly acceptable way to go. — Tim Biegeleisen, Feb 14 '20 at 10:31
Oh ok, so it won't execute the `IN` queries for each `el1,el2`? — Radio Controlled, Feb 14 '20 at 10:39
Please tell us what "some query" is. Please show us the real problem. — Tim Biegeleisen, Feb 14 '20 at 10:46

score 1 · Accepted Answer · answered Feb 14 '20 at 11:31

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

1 Answers1