sql - Join query result with itself in MySQL -

let's have query:

select product_id, price, price_day products price>10 

and want join result of query (if example want in same row product's price , price in previous day)

i can this:

select *  ( select product_id, price, price_day products price>10 ) r1 join  ( select product_id, price, price_day products price>10 ) r2 on r1.product_id=r2.product_id , r1.price_day=r2.price_day-1 

but can see copying original query, naming different name join result itself.

another option create temp table have remember remove it.

is there more elegant way join result of query itself?

self join query help

select a.product_id,a.price       ,a.price_day       ,b.price prevdayprice       ,b.price_day prevday  table1  inner join table1 b  on a.product_id=b.product_id  ,  a.price_day = b.price_day+1    a.price  >10