Previously, I’ve been using separate code for MySQL and SQLite when using the WITH clause and IN operator. I use IN (TABLE cte) in MySQL and IN cte in SQLite.

After seeing dupes like this, and this, I’ve got some idea of how to use WITH clause and IN operator portably – use a SELECT clause. Here’s what I do right now:

MySQL:

WITH cte AS (SELECT column1, column2 FROM Table1 WHERE ...)
SELECT * FROM Table2 WHERE ... AND ((column1, column2) IN (TABLE cte))
ORDER BY column3

SQLite:

WITH cte AS (SELECT column1, column2 FROM Table1 WHERE ...)
SELECT * FROM Table2 WHERE ... AND ((column1, column2) IN cte)
ORDER BY column3

However, I’m a bit worried about performance. If I do the following:

Portable:

WITH cte AS (SELECT column1, column2 FROM Table1 WHERE ...)
SELECT * FROM Table2 WHERE ... AND ((column1, column2) IN (SELECT * FROM cte))
ORDER BY column3

Q1: Would a third select clause (as simple as IN (SELECT * FROM cte)) be burdonsome to execute, if cte was part of the result from the WITH clause? Especially when Table2 is very large?

Q2: For MySQL and SQLite, have they applied any kind of optimization on this?