Full Outer Joins, Coalescing, Indexes, and Views in SQL Server 2012

coalesceindexjoin;sql-server-2012view

I have two tables, let's call them TableA and TableB.

TableA               TableB
------               ------
ID                   ID
Date                 Date
Foo                  Bar

Both tables have an index on their date field.
Now, I want to create a view that has a full outer join between the two tables on the Date field, to show me the Foo value for that date (if there is one) along with the Bar for that date.

create view vFooBar as
select isnull(a.Date, b.Date) Date, a.Foo, b.Bar
from TableA a
full outer join TableB b on a.Date = b.Date

Then I run a query:

select * from vFooBar where Date = '20141201'

This works… but the coalescing of a.Date and b.Date fools the compiler into not using the indexes, and I get a horrible execution plan involving table scans.

How can I create this view in such a way that querying it will still use my indexes?

Best Answer

Try this

select a.Date, a.Foo, b.Bar
from TableA a LEFT JOIN TableB b ON a.Date = b.Date
UNION
select b.Date, a.Foo, b.Bar
from TableB b LEFT JOIN TableA a ON a.Date = b.Date

Strictly speaking, the query above does not quite have the same semantics as the original full join, with respect to duplicates. A correct transformation of full join is to a left join union all anti-semi-join:

SELECT 
    TA.TheDate, 
    TA.Foo, 
    TB.Bar
FROM dbo.TableA AS TA
LEFT JOIN dbo.TableB AS TB 
    ON TB.TheDate = TA.TheDate
UNION ALL
SELECT 
    TB.TheDate, 
    Foo = NULL, 
    TB.Bar 
FROM dbo.TableB AS TB
WHERE NOT EXISTS 
(
    SELECT NULL
    FROM dbo.TableA AS TA 
    WHERE TA.TheDate = TB.TheDate
);

This second rewrite will also facilitate the use of an index on TheDate columns.

Related Question