-1

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!

Luis
  • 1
  • 4
    Correct me if I'm wrong, but wouldn't this be equivalent to `SELECT * FROM crafted_table CROSS JOIN time_table`? I'm trying to imagine a scenario where those would be different. I may just ned more coffee though. – JNevill Feb 25 '22 at 17:26
  • You may need to tell more details to clarify what your problem is. Referencing a table in an SQL statement does not really "run" that table in a negative sense. For example there can be use cases where mentioning the same or another table multiple times in the same statement can help the system to optimize the process of getting the result you are hoping for. – Stefan Wuebbe Feb 25 '22 at 17:47
  • Do you have version 8.0? It has `WITH`. – Rick James Feb 25 '22 at 17:50
  • @JNevill You loose `DISTINCT`. – Akina Feb 25 '22 at 18:22
  • The only difference with cross join I can imagine is the rows where `var1` is null. – Serg Feb 25 '22 at 18:39
  • @Akina I don't think it makes a difference here though. The Distinct is lost just as soon as that table is joined back in on that `var1` key so at the end of the day you get the same output. – JNevill Feb 25 '22 at 18:41
  • What exactly do you mean by *running* a table twice? Also, delete all brackets except those around subquery `b` - they have no effect whatsoever, are code noise and so unconventional they make the query hard to read. – Bohemian Feb 25 '22 at 18:52

2 Answers2

0

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
Luis
  • 1
  • This is identical to the original query. CTEs don't change query behavior, it's just syntactic sugar. Your execution path will be the same. – JNevill Feb 25 '22 at 18:44
0

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

Booboo
  • 38,656
  • 3
  • 37
  • 60