Hacker News Reader

Comment by weaksauce | Hacker News Reader

Aside from using temp tables is there a way to assign sub-queries as a variable and refer to them later on?

completely simple and contrived for example purposes only, something like:

    active_users = select * from users where user.login > 0;

    select * from active_users where active_users.country is not null;

seems like that would clean up the query nesting quite a bit. Might need some kind of begin / end blocks like with transactions so the query planner would know the scope of those sub-queries.

View on HN

Yep, a few.

1. Temporary views. This one's my favourite but not everything supports it and sometimes DBMSs have weird limitations, not like allowing parameter binding in them.

2. Temporary tables, as you mention

3. CTEs ("with expressions"). These would be my favourite if they weren't an optimisation fence in postgres.

    WITH active_users
        AS (select * from users where user.login > 0)

    select * from active_users where active_users.country is not null;

4. If you're only using it once, a subselect lets you add a name to it if that's all you're after

    select *
    from (select * from users where user.login > 0) AS active_users
    where active_users.country is not null;

5. If you're using active_users frequently and want it to be globally accessible, a regular view. Often you can make a DB much more useable with a few well placed views like this and you'll need a few less abstractions as well.

Thanks for the options. can you expand on why CTEs are a postgres optimization fence?

as for 4: I am aware of the named subselect but once you add a few of those to the main select statement you start to get into unreadable territory.

back to CTEs, it seems like it should be an easy substitution problem for the postgres query planner. I am not a DB developer so I am probably not aware of all the issues. The CTE seems like the thing I was trying to describe though. Do you think that the optimizations would be more possible with begin and end scopes? It seems like they are part of the same select statement so i doubt scopes would help it.

In practice optimization fence is not normally a problem, and can even be an advantage. The general rule is - just use CTEs. And the specific rule is - learn to profile and optimize using EXPLAIN ANALYZE

Yes, you can do this using CTEs, i.e. WITH clause, pretty much exactly how you suggest.

Or in the case of your simple query, you may also create and use a view or a user defined table valued function, in addition to a CTE. SQL Server also supports table variables in T-SQL, some other dbs may as well.

You can wrap that first part into a INNER JOIN statement or use a CTE like someone else mentioned

Reply on news.ycombinator.com