WHERE clause on stored result

Question

Let's say I have a query, and I want to store it, something like:

SET @v1 := (SELECT * FROM users);
SELECT @v1;

In some cases, this query would be super complex (a lot of joins, aggregation, etc), and sometimes I need to do the exact same query, but with an added WHERE clause (the query would give a subset of the previous query). Something like:

SELECT * FROM users WHERE isAdmin = true

I do not want to execute the query again and I want to use the results from the previous query (so I can skip executing the long and complex query again), something like (this is not working):

SELECT * FROM @v1 WHERE isAdmin = true

How would I do that?

`SET @v1 := (SELECT * FROM users);` is going to explode. Your last one too — Drew, Oct 12 '16 at 12:22
A [temporary table](http://dev.mysql.com/doc/refman/5.7/en/create-table.html) is used to store temporary table data. You should ensure that there is actually a need for this and that repeating the query is as expensive as you predict. — Alex K., Oct 12 '16 at 12:25
So you may need to look into Cursors which never perform well but get the job done. — Drew, Oct 12 '16 at 12:25

score 0 · Answer 1 · answered Oct 12 '16 at 12:24

0

try to use VIEW:

CREATE VIEW user_view AS SELECT * FROM users;

SELECT * FROM user_view WHERE isAdmin = true;

answered Oct 12 '16 at 12:24

Fujiao Liu

2,195
2
24
28

score 0 · Answer 2 · answered Oct 12 '16 at 12:25

0

create a VIEW for your output

create view vw_temp as SELECT * FROM users // you can add join query also

now call this

SELECT * FROM vw_temp WHERE isAdmin = true

answered Oct 12 '16 at 12:25

jarvis

81
10

if vw_temp is just `select * from users` why bother? – Drew Oct 12 '16 at 12:26
please refer this link http://www.w3schools.com/sql/sql_view.asp – jarvis Oct 12 '16 at 12:30
how does that answer my question :p – Drew Oct 12 '16 at 12:32
you cannot get all the records in @v1, in @v1 you can set only small content eg: `Set @v1 = (select count(*) from user ) OR Set @v1 = (select distinct(column_name) from user )`, in your scenario you have to use view – jarvis Oct 13 '16 at 05:49

score 0 · Accepted Answer · edited May 23 '17 at 12:33

The only thing I can think of is to use a TEMPORARY TABLE (visible only to the current session) e.g.

create temporary table v1 (index (isAdmin)) as
(
  select *
  from users
);

then you can perform a select from this temporary table:

select * from v1 where isAdmin=true;

of course this might be a good idea if the query is complex and takes a lot of time to execute, but it only returns few records. Otherwise it might be more expensive than executing the original query twice (the optimizer might be able to run the second query in less time with proper indexes).

WHERE clause on stored result

3 Answers3