How to write a LEFT OUTER JOIN where the right side is null in LINQ to SQL?

Question

How do you write a LEFT OUTER JOIN in LINQ to SQL, where the right side is null?

A graphical representation of the result I want would be this:

Take this SQL for example:

select Document.*
from Document left outer join Invoice
     on Document.DocumentId = Invoice.DocumentId
where Invoice.DocumentId is null

Basically I want all documents that are not invoices but some other kind of document, doesn't matter what.

I would greatly appreciate examples in both LINQ Query Syntax and LINQ Method (Fluent) Syntax.

Thank you!

Perhaps this will help: [linq-to-sql-left-outer-join](https://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join) — Ryan Wilson, Mar 01 '21 at 16:21

Charlieface · Accepted Answer · 2021-03-01T19:38:38.603

4

Firstly, even in SQL that query should really be a not exists, it is generally more efficient than the left join / is null construct.

The compiler also understands exists better, as it understands that the not exists cannot add more rows to the resultset, so it can keep any uniqueness guarantee that may be there. The compiler does not see that left join with an is null check cannot add rows (perhaps it should, but there is no logic currently built into it to do so).

select Document.*
from Document
where not exists (select 1
    from Invoice
    where Document.DocumentId = Invoice.DocumentId);

Now it's obvious how to do it in Linq:

var docs =
    from doc in Documents
    where !Invoices.Any(inv => doc.DocumentId == inv.DocumentId);

var docs =
    Documents
    .Where(doc => !Invoices.Any(inv => doc.DocumentId == inv.DocumentId));

edited Mar 01 '21 at 19:38

answered Mar 01 '21 at 16:23

Charlieface

52,284
6
19
43

Magnificent Charlie! Thank you. – Paul-Sebastian Manole Mar 01 '21 at 16:28
@charlieface I would suggest reading more about `LEFT OUTER JOIN` in TSQL - [subquery-or-leftouter-join-performance-wise](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0a4e384c-f8c6-45b7-a4db-c14827500fe3/subquery-or-leftouter-join-performance-wise?forum=transactsql). While your answer works, I don't believe your statement about the `LEFT JOIN` being less efficient is correct. – Ryan Wilson Mar 01 '21 at 19:23
@RyanWilson You can start by reading [this article by Aaron Bertrand](https://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join) on the subject. I will also tell you that the compiler often gains an advantage when it can guarantee that a particular join does not add more rows to the previous result. It can only do this for a semi- or anti-join, not for the left join/is null construct. I would agree with you that the there should be such logic in to the compiler, but today it does no exist. And I read that thread to the end, but can't see anything in it to change my mind. – Charlieface Mar 01 '21 at 19:38
@RyanWilson Have clarified it further though – Charlieface Mar 01 '21 at 19:39
@Charlieface Thanks for the article, I read through it. Just going to point to this [sql-performance-on-left-outer-join-vs-not-exists](https://stackoverflow.com/questions/6777910/sql-performance-on-left-outer-join-vs-not-exists), as of SQL Server 2017, possibly earlier, it converts the LEFT OUTER JOIN to anti semi join, so it can shortcircuit as well. Also, it depends on if the column you are looking at can be NULL as NOT EXISTS / EXISTS doesn't work on NULLs. Point being, there are cases where each is preferrable. – Ryan Wilson Mar 01 '21 at 20:29
That's an interesting claim by @andowero. I would love it to be so, need to test. – Charlieface Mar 01 '21 at 20:46
@RyanWilson I get a left join plan on [SQLfiddle which is 2017](http://sqlfiddle.com/#!18/9eecb/113717/0) – Charlieface Mar 01 '21 at 20:56
@Charlieface, your answer is perfect. But for completeness' sake, can you edit it and also provide an answer that is closer to the question? I am now convinced that `where not exists` is the correct way to write the query I wanted, but other people might come here and expect to find the correct way to express an actual `left outer join` and digging through the comments might be cumbersome for them. I believe [this answer](https://stackoverflow.com/a/700580/267874) shows what an actual `left outer join` looks like in LINQ-to-SQL. – Paul-Sebastian Manole Mar 02 '21 at 08:48
1

Indeed, that is the way to do it for a regular left join, and there are a hundred such posts on [so] already. But your question doesn't say that, your question says "How do you write a LEFT OUTER JOIN in LINQ to SQL, where the right side is null?" and the answer to that is to rewrite it as a `not exists` – Charlieface Mar 02 '21 at 09:22

How to write a LEFT OUTER JOIN where the right side is null in LINQ to SQL?

1 Answers1