SQL Server: How to do old and new style left joins

I like using the newer ANSI-92 standard JOIN statements, and I believe that once you get used to them, they are easier to understand. They certainly are easier to read, because they separate the function of joining tables from the filtering taking place in the where clause.

That said, I get SELECT statements out of applications sometimes by running a trace, just to see what is going on in an application. A lot of times, these SQL statements don't use the new standards. You can't use old style left joins with the new ANSI-92 standard.

So this code shows how to use both styles. I create a couple of temporary tables for this example, one called MASTER and the other SLAVE. Our objective is to see all rows of MASTER, no matter whether there are matching rows in SLAVE:

-- Create and populate our temporary tables
create table #master (id int)
create table #slave (id int, descrip varchar(32))

insert into #master values (1)
insert into #master values (2)
insert into #master values (3)

-- Note that we don't put a description for 2
insert into #slave values (1, 'one')
insert into #slave values (3, 'three')

-- old left join-- the * goes on the "MASTER" side of the equal sign
select m.id, s.descrip
  from #master m, #slave s
 where m.id *= s.id

id	descrip
1	one
2	NULL
3	three

-- ANSI-92 left join
select m.id, s.descrip
  from #master m
  left join #slave s on s.id = m.id

id	descrip
1	one
2	NULL
3	three

-- Drop our temporary tables
drop table #master
drop table #slave

These two SELECT statements produce identical output. I won't say that these two JOIN styles are identical-- there are examples of how they differ out there somewhere. But if you're stuck using the old style, it helps to know the syntax.