Suppose I have a table Entry. Is it valid for a WITH clause to replace the Entry table for the duration of a query? I tried with SQLite and MySql and it seems to work, but I want to be sure this is intentional before I use it in an application.

Let me add some detail. Say I have a table Entry and another table Thing, and a query like this

SELECT Entry.k, Entry.v, Thing.v2
FROM Entry INNER JOIN Thing ON Entry.v = Thing.k

Is the following valid?

WITH Entry AS (SELECT 1 AS k, 1234 AS v)
SELECT Entry.k, Entry.v, Thing.v2
FROM Entry INNER JOIN Thing ON Entry.v = Thing.k

This seems to do what I want, but can I count on this behavior not being a bug or undefined behavior? Can't find anything in the documentation of either SQLite or MySQL explicitly allowing or forbidding it.

(I know in this particular example I can just SELECT ... FROM Thing WHERE .... That's not the point of this question - please trust me that I have a good reason for writing the query I'm writing :) )


1 Answer 1


You can rest assured that this is not undefined behaviour.

From the official MYSQL documentation

For resolving references to objects with the same names, derived tables hide CTEs; and CTEs hide base tables, TEMPORARY tables, and views. Name resolution occurs by searching for objects in the same query block, then proceeding to outer blocks in turn while no object with the name is found.

So in terms of name resolution priority:

derived tables > CTEs (table defined in a WITH block) > everything else

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.