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.

  • 3
    Condition t2.ShardKey = @shardkey which appears in WHERE in both cases "turns" your queries into inner joins... – a1ex07 Oct 6 '15 at 14:53
  • That was a mistake. Should not be there. I edited my question. – annemartijn Oct 6 '15 at 14:55
  • 1
    The 2 queries are not equivalent. – ypercubeᵀᴹ Oct 6 '15 at 16:07
  • 1
    Sorry but either they are equivalent (and always return the same result) or they are not (and they sometimes return different result). – ypercubeᵀᴹ Oct 6 '15 at 16:20
  • 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. – ypercubeᵀᴹ Oct 6 '15 at 20:02
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

  • I agree it would be (at least semantically) better to just add any LEFT OUTER JOIN restrictions to the ON clause, but that's a limitation of the ORM(NHibernate) I have to work with in my particular cases. Since your answer is based on suspicion, I will not mark your answer as accepted answer just yet. I'll wait for someone to give me a conclusive answer if fiddling around with OR will give me a different query plan in some given situation. I'm sorry for all the edits I needed to get the question right. – annemartijn Oct 6 '15 at 17:33
  • @annemartijn I created the actual tables and viewed the plans. I'm seeing a different plan, with your ORM version being an inferior plan to the standard LEFT JOIN approach, both in terms of simplicity and estimated cost (by a slight margin). I updated my answer to reflect this. Feel free to wait for additional input from others though! – Geoff Patterson Oct 6 '15 at 17:53
  • @ypercube thanks, very helpful. I updated my answer. I had been naively assuming that the same sharding methodology is applied to an Id regardless of whether it goes into Table1 or Table2, but that's not necessarily a valid assumption. It may or may not hold true in the OP's real data, but there is certainly nothing in the data model to have warranted that erroneous assumption. – Geoff Patterson Oct 6 '15 at 20:14
  • @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. – annemartijn Oct 7 '15 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.