Что происходит при LEFT JOIN, если в правой таблице нет соответствующих значений

Что проверяют

Понимаете ли вы семантику внешнего соединения и как она выражается в LINQ и EF Core, где NULL из базы встречается с системой типов C#. Вопрос про SQL, но отвечать на нём выгодно на своём языке.

Как отвечать

LEFT JOIN отдаёт все строки левой таблицы, и там, где пары справа нет, колонки правой приходят как NULL. Строк не убавляется, а при связи один ко многим может прибавиться.

В LINQ внешнее соединение пишется явно:

var q = from c in db.Customers
        join o in db.Orders on c.Id equals o.CustomerId into g
        from o in g.DefaultIfEmpty()
        select new { c.Name, OrderId = (int?)o.Id };

DefaultIfEmpty и делает соединение внешним. В EF Core чаще достаточно навигационного свойства: обращение к необязательной связи переводится в LEFT JOIN само.

Про типы скажите отдельно: пустое значение требует int?, DateTime? и приведения в проекции, иначе материализация упадёт. На голом ADO.NET проверяйте reader.IsDBNull(i), потому что DBNull.Value к int не приводится.

Где спотыкаются

Дописывают условие по правой таблице в Where после соединения. Сравнение с NULL даёт неопределённость, а не истину, строки без пары отсеиваются, и внешнее соединение превращается во внутреннее. Условие принадлежит части on либо пишется с явной проверкой на null.

Забывают, что NULL не равен NULL. Один и тот же запрос над коллекцией в памяти и над базой даёт разный результат: в LINQ к объектам два пустых значения равны, в SQL сравнение возвращает неопределённость.

Агрегируют по пустой стороне без оглядки на типы. Сумма по пустому набору в SQL это NULL, и разворачивание в int кидает исключение. Лечится приведением к int? и оператором ??.