Is a JOIN subquery, created once as a table or is it reevaluated each time?

Question

Say I have the following as a join in a larger query aliased as 'overall'

        INNER JOIN (SELECT
                    tts.typ,tts.amount,
                    tts.term_sale_id,
                    tts.term_sale_id,
                    tts.from_price
                FROM tb_term_sale_entries tts
                WHERE tts.start_date <= '#dateformat(now(),'mm/dd/yyyy')# 23:59:00'
                AND tts.end_date >= '#dateformat(now(),'mm/dd/yyyy')# 23:59:00'
                AND tts.style_id = overall.style_id) term_sale ON (tts.style_id = overall.style_id)

When SQL is handling this query, does it create the term_sale table one time and then join it as needed, or does it create term_sale for each row of the main query?

In the above, I have the join condition twice, once in the subquery and once outside in the join statement. My question is which is generally more efficient?

it's up to the SQL engine. Why do you care? If your database server has an 'explain command' feature, you'll be able to see what steps it takes — sehe, Aug 02 '13 at 15:49

score 1 · Accepted Answer · edited May 23 '17 at 12:05

Viewing the query execution plan (How do I obtain a Query Execution Plan?) should help you determine which of the two options will be more efficient.

In this case though, I'm 99% that you are going to want to keep your condition inside the subquery, thereby limiting that result set, which should make the join and query more efficient. Basically, it's better to join to a smaller table / result set rather than a larger one.

score 1 · Answer 2 · answered Aug 02 '13 at 16:58

1

As it is treated as Sub query SQL Engine executes the term_sale and operates on the data set that has been created after the execution of this query.Only the comparison part i.e. On part is done row by row.

regards

Ashutosh Arya

answered Aug 02 '13 at 16:58

Ashutosh Arya

1,138
2
8
14

Gordon Linoff · Answer 3 · 2013-08-02T19:11:00.327

Assuming that this is taken from the from clause of a query (which is a highly reasonable assumption given that it starts with inner join), the statement as presented is incorrect SQL:

    INNER JOIN
    (SELECT tts.typ,tts.amount, tts.term_sale_id, tts.term_sale_id, tts.from_price
     FROM tb_term_sale_entries tts
     WHERE tts.start_date <= '#dateformat(now(),'mm/dd/yyyy')# 23:59:00' and
           tts.end_date >= '#dateformat(now(),'mm/dd/yyyy')# 23:59:00' and
           tts.style_id = overall.style_id and b.store_id = 99
--------------------------^ UNDEFINED ---------^ UNDEFINED
   ) term_sale
   ON term_sale.style_id = overall.style_id

This is referencing two table aliases that do not exist in the scope of the subquery. I'm not sure what database you are using, but I don't know of any that have scoping rules (in violation of the standard) that allow this.

You can do this in a correlated subquery in a SELECT, WHERE, or HAVING clause. If this is the case, you should show more of the overall query. The snippet you have does not look like valid SQL syntax.

Bah you are correct. This was taken from a much larger query and I thought I removed all the unnecessary aliases. The main query would be overall and b is ofc another joined table. — Magic Lasso, Aug 02 '13 at 19:09

Is a JOIN subquery, created once as a table or is it reevaluated each time?

3 Answers3