Search code examples
sqlitesetinner-joinresultsetexists

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?

Solution

  • 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