CODEWITHSUNDEEP
sqljoinsubquery
Magic Lasso
Magic Lasso

Reputation: 1542

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

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?

Upvotes: 0

Views: 71

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

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.

Upvotes: 0

Ashutosh Arya
Ashutosh Arya

Reputation: 1168

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

Upvotes: 1

Dudeman3000
Dudeman3000

Reputation: 601

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.

Upvotes: 1

Related Questions