Try to define a left join only with where condition

I try to find a particular SQL statement to replace an old SQL query. To summarize, I try to make a left join only with where conditions. Here is my test environment:

create table Mst
(
    Id bigint not null primary key clustered,
    Firstname nvarchar(200) not null,
    Lastname nvarchar(200) not null
);
create table  Dtl
(
    Id bigint not null primary key clustered,
    MstId bigint not null references Mst(Id),
    DetailDescr nvarchar(500) not null
);

I fill the tables with some data:

declare @i as bigint = 0;
while @i < 999
begin
 insert into Mst values (@i, N'Name ' + Str(@i), N'Lastname ' + Str(@i));
 if (@i % 10 = 0)
    insert into Dtl values (@i*5+0, @i,  N'Description 1 for ' + Str(@i));
 if (@i % 2 = 0)
    insert into Dtl values (@i*5+1, @i,  N'Description 2 for ' + Str(@i));
 if (@i % 3 = 0)
    insert into Dtl values (@i*5+2, @i,  N'Description 3 for ' + Str(@i));
 set @i = @i + 1;
end;

The usual way for a left join is this:

select m.Id, m.Firstname, m.Lastname, d.DetailDescr
From Mst m left join Dtl d
on m.id = d.MstId;

This query returns 1266 rows. But in the old application, which I try to migrate, the select- and from-part is still predefined:

select m.Id, m.Firstname, m.Lastname, d.DetailDescr
From Mst m, Dtl d

The old where condition defines (in a separate software module) a no longer available LEFT JOIN:

where m.id *= d.MstId

So we have to migrate that approach and try to modify only the where condition if possible. For an inner join, the where condition is easy to define:

where m.id = d.MstId

But I need a left join, and I find no way with only modify the where condition. But to rewrite only the where-condition is the best way in that special application.

Thanks in advance for your ideas.

Answer

Once upon a time, SQL did not support outer join syntax. It was an ancient world, where telephones were connected by wires to walls, where counties in Europe each had their own currencies, and most Americans watched one of three or four major networks on television.

At that time, Microsoft did not even have a real database. But Sybase offered an outer join operator in the WHERE clause, *=, which Microsoft eventually adapted into SQL Server. Microsoft SQL Server supported this through SQL Server 2008. Hence, no supported version of SQL Server supports outer joins in the WHERE clause.

Happily a much better standard syntax now exists (lest we be despondent and think that things do not get better over time). The “comma operator” in the FROM clause is relegated to its original definition — a CROSS JOIN. The CROSS JOIN filters out non-matches. For instance, if Dtl has no rows, then CROSS JOIN returns no rows.

That is, there is no way to do what you want generically in the WHERE clause. There are queries that can replicate an outer JOIN, but they require much more surgery to the query. But there is a good alternative, which is to write your queries with the correct, modern syntax.