CODEWITHSUNDEEP
sqlsql-serverquery-optimization
daniel_dutra
daniel_dutra

Reputation: 104

which is faster, left join the whole table, or left join a subselection of said table?

I have two tables (A & B) my objective is to have some columns from A left joined with a few columns of B (both tables have a LOT of columns)

is it faster to :

A) Select A -> left join -> subselect B: (selecting only the desired columns BEFORE the join)

SELECT * FROM (
    SELECT A.col_1,A.col_2,A.col3,A.col_b FROM A
    LEFT JOIN (
        SELECT B.col_1,B.col_2,B.col_a FROM B) B_temp
    ON A.col_b = B_temp.col_a

B) Select A -> left join -B: (selecting only the desired columns AFTER the join)

SELECT A.col_1,A.col_2,A.col3,B.col_1,B.col_2 FROM A
LEFT JOIN B
ON A.col_b = B_temp.col_a

My gut tells me even tho the second option is way more readable, it might be worse since it first aglutinates everything moving a lot of data around. My consideration for this is:

  1. If the left join returns many results the simple-trivial approach (option B) might have to carry all these extra unecessary columns

Am I going in the right-way towards optimizing this sql query ?

Upvotes: 0

Views: 64

Answers (1)

O. Jones
O. Jones

Reputation: 108641

Unless your SQL software is old and moldy, its query planner will handle your two example queries the same way.

Upvotes: 1

Related Questions