Optimizing the execution plan of SQL Server query with subquery in where condition

Question

There are two tables one with the actual fact data(table1) and the other table(table2) with more like a tracker information on date. I am trying to get all the fact data from table1 where the date field is less than max(date) from table 2. Following is the query I am using

SELECT * FROM TABLE1 WHERE date < SELECT MAX(date) from TABLE2

When I check the execution plan, I see SQL server is trying to optimize the query by using an inner join instead of straightforward filter condition

When I replace the same subquery with a value, the execution plan shows a straightforward filter

SELECT * FROM TABLE1 WHERE date < '2023-03-01'

My understanding is that an inner join is a costly operation when compared with a filter. How can I optimize the where condition with a subquery to avoid inner join and run it before the main query and provide it as a value in the final where condition

Answer 1

My understanding is that an inner join is a costly operation when compared with a filter

This is categorically not true. Joins can often be efficient when used correctly.

The reason this query is slow is not because of the join. The join is only presented with a single outer row, whose single value is pushed down to the lower side as an outer reference.

The real reason is because the tables are not indexed properly (or at all). You probably need the following indexes for this to work well

CREATE CLUSTERED INDEX CX ON TABLE1 (date);
CREATE NONCLUSTERED INDEX IX ON TABLE2 (date);

Table1 needs to have a clustered index because you are querying all columns. You can obviously also use a non-clustered index with INCLUDE on all columns but that seems pointless. If you narrow down the columns you are querying then you may be able to get away with an NCI and INCLUDE.

Table2 does not need a clustered index for this query as you are only querying the one column. But it may make sense to make this a clustered index anyway.

Primary keys are by default a clustered index, so you may need to rebuild it.