How to use a table twice without run it 2 times?-CodePudding

Right now I got a structure that outputs exactly the result I'm looking for, however I think it isn't as optimized as I would like to since it runs the same table twice (crafted_table). Here's the structure of my query:

SELECT *
FROM (crafted_table) c

RIGHT JOIN 
(
        SELECT * FROM 
        (
                SELECT DISTINCT a.var1
                FROM (crafted_table) a 
        )
        CROSS JOIN (time_table)
) b

ON c.var1 = b.var1

Is there a way to run this same query without running crafted_table twice? (crafted_table is a table made by me from other tables). I was thinking something about running the most inner case and just reference it in the most outer case, but not quite sure how to achieve it.

Thanks!

CodePudding user response：

Update: Thank you @Rick James for your answer, it works. Thanks to all contributors as well for the comments. WITH works perfectly fine in this instance, I wasn't really aware of the command. Here's the solution:

WITH cool_table AS (crafted_table)

SELECT *
FROM cool_table c

RIGHT JOIN 
(
        SELECT * FROM 
        (
                SELECT DISTINCT a.var1
                FROM cool_table a 
        )
        CROSS JOIN (time_table)
) b

ON c.var1 = b.var1

CodePudding user response：

If you're running MySql 8.0, which supports Common Table Expressions, then you would do something like the following (I have made a few corrections to the SQL below and replaced time_table with another reference to crafted_table just to keep the number of distinct tables to 1 for demo purposes):

with crafted_table as (
   /*
     select statement for constructing the relation crafted_table
     would go here, for example:
   */
   select 1 as var1 union all select 2 union all select 3
)
SELECT *
FROM crafted_table c
RIGHT JOIN 
(
        SELECT x.var1, y.var1 as var2 FROM 
        (
                SELECT DISTINCT a.var1
                FROM crafted_table a 
        ) x
        CROSS JOIN crafted_table y on x.var1 = y.var1
) b
ON c.var1 = b.var1

See db fiddle