How to simulate LEFT JOIN as INNER JOIN in SQL Server while creating indexed view

Question

It is possible to simulate left join using inner join while creating indexed view in SQL Server? I found a solution using ISNULL(id,0) but it does not work for me.

I have Table1 (ID PK, ID_U FK null) and I want to left join Table2 (ID_U PK, VALUE). I want to see all rows from Table1 and associated rows from Table2 if exist ID_U.

In created view script I have

select t1.ID, t1.ID_U, t2.VALUE 
from Table1 t1 
join Table2 t2 on t1.ID_U = t2.ID_U 
               or (ISNULL(t1.ID_U, 0) = 0 and ISNULL(t2.ID_U, 0) = 0)
where t1.ID_U is null -- only for test

/* create clustered index */

As a result I should see all rows from Table1 where ID_U is null with nulls in right table, but I have 0 rows.

What am I doing wrong?

Answer 1

I do not recommend you do this.

You really should not do this.

Not even sure if SQL will allow you to create an indexed view doing this - hope not.

But, if you absolutely have to make an INNER act like a LEFT JOIN, then here's one way...

select 
    t1.ID, 
    t1.ID_U, 
    iif(t1.ID_U IS NULL, NULL, t2.Value) as Value  -- return NULL when joined with the dummy record
from Table1 t1 
join Table2 t2 on t1.ID_U = t2.ID_U 
               or ISNULL(t1.ID_U, 1) = t2.ID_U  -- this joins the same row from t2 (with ID_U = 1) to every row in t1 that has a ID_U of NULL .  NOTE:  There must be a row in t2 with ID_U = 1