For some irrelevant reasons it's hard for me to get LEFT OUTER JOINS
working with multiple ON
clauses. Since simply moving these clauses to the WHERE
statement results in different result sets, I add an additional OR IS NULL
check.
LEFT OUTER JOIN ON query
SELECT t1.id, t2.ShardKey
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 on t1.table2 = t2.id and t1.ShardKey = t2.ShardKey
WHERE t1.id = @id and t1.ShardKey = @shardkey
LEFT OUTER JOIN WHERE OR IS NULL query
SELECT t1.id, t2.ShardKey
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 on t1.table2 = t2.id
WHERE t1.id = @id and t1.ShardKey = @shardkey
AND (t1.ShardKey = t2.ShardKey OR t2.ShardKey IS NULL)
Simplified definition for both tables:
CREATE TABLE [dbo].[Table1](
[Id] [int] NOT NULL,
[ShardKey] [int] NOT NULL,
[Table2] [int] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC,
[ShardKey] ASC
))
CREATE TABLE [dbo].[Table2](
[Id] [int] NOT NULL,
[ShardKey] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC,
[ShardKey] ASC
))
These queries seem to give exactly the same query plan in SQL Server Management Studio, but I wonder if there're situations where the one outperforms the other.