Applying WHERE before JOIN

1

Hello

When we do:
select * from Table1 left outer join (select * from Table2) where Table1.[Col1]=1

Is it possible to always/force run the WHERE before doing the join?
In this case the WHERE filter is on Table1 so it will be more efficient to join the filtered Table1 rather than the full Table1 and then apply the WHERE.

Thanks!

2

The optimizer will convert the query into physical data operations (scans, seeks, nested loops vs. merge vs. hash joins, etc.). The order of operations in the written query is immaterial, as long as it is syntactically valid.

You can always post your query execution plans here and we can help with tuning them. The first suggestion I would make is to stop using SELECT * from either table. Only SELECT the actual columns you need, which will more likely use existing indexes. SELECT * will almost certainly have to do a table scan, even if a useful index exists.

Edit: additionally, the way your LEFT JOIN is declared on a subquery will lead to a cartesian product on your data, meaning Table1 row count * Table2 row count will be returned. There's no JOIN condition between the two.

1 Like
3

There's no way to force SQL to do it first, but this would likely give you the best chance:

select * from ( select * from Table1 where col1 = 1 ) as t1 left outer join (select * from Table2) as t2 on t2.column = t1.column

1 Like
4

Thanks, that's was I had done but it takes quite long (>10min for 1.5mil records) :frowning:

5

Ah yeah, forgot the ON, I include it in the real query, thanks

6

This might be a case where divide & conquer is the best option. What that means is - select from table1 with the where clause into a temp table, then join from temp table to table2.

It may also need a clustered index defined on the join column(s) - which you can do by defining the temp table before inserting into the table from your select statement. I would test using select into first - and if it is still too slow then create temp table and insert.

1 Like