Querying Entity Model. Part 19 – Full Outer Join – Andrey Zavadskiy

There is a rarely used join in SQL as full outer join. It combines the intersection between two sets, and both set exception from left and right parts of the join. Full outer join might be used to merge data from source and target tables, so you would get intersecting rows to update, new rows to insert, and missing rows to delete.

This type of join also can be implemented by LINQ queries on Entity Framework in four steps:

Left outer join
Right outer join
Concat both outer joins
Get distinct result

var query19l = ctx.Clients
    .SelectMany(
        client => ctx.Phones.Where(phone => client.CountryCode == phone.CountryCode).DefaultIfEmpty(),
        (client, phone) => new { client, phone }
    );

var query19r = ctx.Phones
    .SelectMany(
        phone => ctx.Clients.Where(client => client.CountryCode == phone.CountryCode).DefaultIfEmpty(),
        (phone, client) => new { client, phone }
    );

var query19 = query19l.Concat(query19r).Distinct();

var query19l = ctx.Clients

.SelectMany(

client => ctx.Phones.Where(phone => client.CountryCode == phone.CountryCode).DefaultIfEmpty(),

(client, phone) => new { client, phone }

);

var query19r = ctx.Phones

.SelectMany(

phone => ctx.Clients.Where(client => client.CountryCode == phone.CountryCode).DefaultIfEmpty(),

(phone, client) => new { client, phone }

);

var query19 = query19l.Concat(query19r).Distinct();

Here is an abridged T-SQL code (I’ve deleted extra lines in SELECT clause for readability).

SELECT 
    [Distinct1].[C1] AS [C1], 
    [Distinct1].[C2] AS [C2], 
    ...
    FROM ( 
        SELECT DISTINCT 
            [UnionAll1].[C1] AS [C1], 
            [UnionAll1].[Id] AS [C2], 
            ...
        FROM  (SELECT 
            1 AS [C1], 
            [Extent1].[Id] AS [Id], 
            [Extent1].[Name] AS [Name], 
            ...
            FROM  [dbo].[Clients] AS [Extent1]
            LEFT OUTER JOIN [dbo].[Phones] AS [Extent2] ON (Extent1].[CountryCode] = [Extent2].[CountryCode]) 
                OR (([Extent1].[CountryCode] IS NULL) AND ([Extent2].[CountryCode] IS NULL))
        UNION ALL

        SELECT 
            1 AS [C1], 
            [Extent4].[Id] AS [Id], 
            [Extent4].[Name] AS [Name], 
            ...
        FROM  [dbo].[Phones] AS [Extent3]
        LEFT OUTER JOIN [dbo].[Clients] AS [Extent4] ON ([Extent4].[CountryCode] = [Extent3].[CountryCode]) 
            OR (([Extent4].[CountryCode] IS NULL) AND ([Extent3].[CountryCode] IS NULL))
        ) AS [UnionAll1]
    )  AS [Distinct1]

SELECT

[Distinct1].[C1] AS [C1],

[Distinct1].[C2] AS [C2],

...

FROM (

SELECT DISTINCT

[UnionAll1].[C1] AS [C1],

[UnionAll1].[Id] AS [C2],

...

FROM (SELECT

1 AS [C1],

[Extent1].[Id] AS [Id],

[Extent1].[Name] AS [Name],

...

FROM [dbo].[Clients] AS [Extent1]

LEFT OUTER JOIN [dbo].[Phones] AS [Extent2] ON (Extent1].[CountryCode] = [Extent2].[CountryCode])

OR (([Extent1].[CountryCode] IS NULL) AND ([Extent2].[CountryCode] IS NULL))

UNION ALL

SELECT

1 AS [C1],

[Extent4].[Id] AS [Id],

[Extent4].[Name] AS [Name],

...

FROM [dbo].[Phones] AS [Extent3]

LEFT OUTER JOIN [dbo].[Clients] AS [Extent4] ON ([Extent4].[CountryCode] = [Extent3].[CountryCode])

OR (([Extent4].[CountryCode] IS NULL) AND ([Extent3].[CountryCode] IS NULL))

) AS [UnionAll1]

) AS [Distinct1]

As you see, T-SQL code repeats the logic of LINQ. Comparison of execution plans shows that a native FULL OUTER JOIN has cost 36% against 64% of LINQ in my case. Maybe you can get a bigger difference.

And native T-SQL code is more elegant 🙂

SELECT *
FROM Clients c
FULL OUTER JOIN Phones p ON c.CountryCode = p.CountryCode;

SELECT *

FROM Clients c

FULL OUTER JOIN Phones p ON c.CountryCode = p.CountryCode;

Leave a Reply Cancel reply