CODEWITHSUNDEEP
mysqlsqlstored-procedures
simd
simd

Reputation: 2019

Use already returned SELECT in another SELECT in stored procedures

I have a stored procedure that returns two result sets. But second SELECT is related on first by join.

Is there a way in MySQL to get rid of repeating? How could I address a SELECT that already was done? Would it increase performance? 

CREATE PROCEDURE `procedure`()
BEGIN
    SELECT * FROM users WHERE age > 20;
    SELECT item_id FROM items INNER JOIN (SELECT * FROM users WHERE age > 20) ON user_id;
END

Upvotes: 0

Views: 61

Answers (1)

Drew
Drew

Reputation: 24959

create table items
(
    item_id int auto_increment primary key,
    user_id int not null
    -- FK not shown
);

create table users
(
    user_id int auto_increment primary key,
    age int not null
);

insert items(user_id) values (1),(1),(2);
insert users (age) values (22),(1);

Data at the moment

select * from items;
+---------+---------+
| item_id | user_id |
+---------+---------+
|       1 |       1 |
|       2 |       1 |
|       3 |       2 |
+---------+---------+
select * from users;
+---------+-----+
| user_id | age |
+---------+-----+
|       1 |  22 |
|       2 |   1 |
+---------+-----+

Query

SELECT item_id FROM items 
join
(   select user_id
    from users
    where age>20
) xDerived
on xDerived.user_id=items.user_id;

+---------+
| item_id |
+---------+
|       1 |
|       2 |
+---------+

So there, that is your query. One query, 1 result set.

Depiction of temp tables

This is a view of things of temp tables based on op questions above (not below).

drop procedure if exists proc123;
delimiter $$
create procedure proc123 (iParam int)
begin
    insert myTempThing (theI,theWhen) select iParam,now();
end
$$
delimiter ;

-- the following is a temp table NOT created inside a stored proc
-- as in, via whatever programming language you are using
-- will incur time overhead especially if indexes are used, but regardless
create temporary table myTempThing
(   id int auto_increment primary key,
    theI int not null,
    theWhen datetime not null
);

insert myTempThing (theI,theWhen) select 123,now();
-- now wait a bit and run the call stmt below

-- again I am still at a workbench prompt

call proc123(456);

select * from myTempThing;
-- two rows

Upvotes: 1

Related Questions