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 :) )