samedi 30 janvier 2016

Using an alias defined in `FROM` part, instead of a column result sub‑query: is it possible?

Question

For readability mainly, while I know I could achieve the same with a TEMPORARY TABLE, I would like to avoid the latter (personal preference to have the whole in a single query).

The question is asked in the context of standard SQL.

Abstract case

Say I have something about like this:

SELECT a, (a IN (SELECT … )) as b,
FROM t

Is there any way to have something like this instead:

SELECT a, (a IN u) as b,
FROM t, (SELECT … ) as u

If I do this, the database engine (which is actually SQLite, for the anecdote), complains the table u is unknown. I though is would be visible, as it would be possible to use u as a column prefix.

I know I can do this:

CREATE TEMPORARY TABLE IF NOT EXISTS u AS SELECT … ;
SELECT a, (a IN u) as b,
FROM t

However, as I said above, I would like to avoid it, as I want a monolithic query (due to personal preferences).

That's mainly for readability when the sub‑query is a bit large, and it does not need to be a lot large, to prevent good readability.

Aucun commentaire:

Enregistrer un commentaire