Consider the following two queries from the AdventureWorks database. The where criteria is entirely on a primary key column. If you use OR criteria both tables get a full table scan on them. In the second scenario the criteria is split out over two queries that are unioned. In the second scenario both tables get index seeks. Can someone explain why SQL can’t/won’t optimize the first query to do in index seek? Are there ways to tell SQL to do a seek without rewriting as a UNION? (I’ve tried index hints with no success)

SELECT *
FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] hdr
join [AdventureWorks2022].[Sales].[SalesOrderDetail] dtl on hdr.SalesOrderID = dtl.SalesOrderID
where hdr.SalesOrderID = 43659 or dtl.SalesOrderDetailID = 43659

SELECT *
FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] hdr
join [AdventureWorks2022].[Sales].[SalesOrderDetail] dtl on hdr.SalesOrderID = dtl.SalesOrderID
where hdr.SalesOrderID = 43659

UNION

SELECT *
FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] hdr
join [AdventureWorks2022].[Sales].[SalesOrderDetail] dtl on hdr.SalesOrderID = dtl.SalesOrderID
where dtl.SalesOrderDetailID = 43659