2

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.

5
  • 1
    The 2 queries are not equivalent. Oct 6, 2015 at 16:07
  • @ypercube they are not, but they should result in the same result set. I want to know if there are situations where they will have different performance. Oct 6, 2015 at 16:11
  • 1
    Sorry but either they are equivalent (and always return the same result) or they are not (and they sometimes return different result). Oct 6, 2015 at 16:20
  • Could you please explain in what situation these queries return different sets so I can see what you're trying to explain to me? Please keep in mind that the column Shardkey is not nullable and t2.shardkey in the second query will only be null if there is no t2 found that meets the conditions set. Oct 6, 2015 at 16:30
  • 1
    Is this a good enough example of the queries returning different results?: SQLfiddle. Unless there is some FK relationship between the 2 tables, the queries are not equivalent. Oct 6, 2015 at 20:02

1 Answer 1

4

The LEFT OUTER JOIN ON version is the standard way to write this logic, so I would highly recommend using this version rather than fiddling around with OR for performance reasons.

However, as ypercube has helpfully pointed out, the queries are not quite equivalent if the data and sharding methodology means that a Table1.Id value can end up in a different ShardKey than the ShardKey of the corresponding Table1.table2 value. If that happens, then the OR t2.ShardKey IS NULL portion of the 2nd query will be triggered and retain a row with matching Ids even if the ShardKey does not match.

In general, I suspect that they will typically result in similar query plans. Given two equivalent queries (which yours may or may not be, depending on the sharding methodology), by formulating the query in the standard way you are more likely to be working with the query optimizer rather than forcing it to recognize that the two queries can be expressed in similar ways and apply the appropriate simplifications.

If you want to be even more explicit about applying @shardkey to each table in the join, you can do that directly in the LEFT OUTER JOIN. However, SQL Server will be able to easily recognize that your original query is equivalent to this one.

SELECT t1.id, t2.ShardKey
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 on t1.table2 = t2.id and t2.ShardKey = @shardkey
WHERE t1.id = @id and t1.ShardKey = @shardkey


An example of different query plans

On my SQL 2014 instance, the queries have (slightly) different query plans and the query plan for the LEFT OUTER JOIN ON version (top) is superior to the ORM-generated LEFT OUTER JOIN WHERE OR IS NULL version (bottom).

In particular, the seek into Table2 should be a seek on both id and ShardKey, but in the LEFT OUTER JOIN WHERE OR IS NULL version it is a seek only on id and a subsequent Filter operator tests all matching rows in order to apply the appropriate ShardKey restriction.

This difference may or may not be meaningful in your specific case (especially if Id is unique without adding in the ShardKey). However, if your real version actually uses table partitioning by ShardKey (and this just isn't represented in the simple sample query), you will likely be losing the benefit of partition elimination for the seek into Table2.

enter image description here

1
  • @GeoffPatterson @ypercube thanks for the extra insight. Id is unique (and indeed an unique identifier) for every table and both tables are partitioned by the same Partition function on ShardKey. I did not want the question to be too broad, but I do think now, that I did leave some relevant things out. There also is a foreign key relationship between Table1 and Table2. Oct 7, 2015 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.