old-school left join, too quirky, sometimes result incorrectly. that's why ansi sql encourage the new left join

create table x
(
a int,
b int
)


create table y
(
a int
)


set nocount on

declare @a int, @b int

set @a = 1
while @a < 20 begin
insert into x(a,b) select @a, @a + 500000
insert into y(a) select @a / 2
set @a = @a + 1
end

insert into y(a) select null

-- query 1: using subquery
select *
from x
where x.a not in(select a from y where a is not null)
order by x.a

-- this join is equivalent to query 1 and is faster
select x.*
from x
left join y on x.a = y.a and y.a is not null
where y.a is null
order by x.a


/*
Note:

-- old way of left joining:
select x.* from x, y where x.a *= y.a

-- new way of left joining(ansi sql compliant):
select x.* from x left join y on x.a = y.a


but when you put other conditions in where clause in old-fashion left join(the *= ), things will get quirky, as you will see in the next queries result

*/

-- incorrect result, the quirks of old-way left join, not equivalent to query 1
select x.*, ya = Y.A
from x, y
where x.a *= y.a and y.a is null


-- incorrect result, still not equivalent to query 1
-- too quirky
select
*
from
(
select x.*, ya = Y.A
from x, y
where x.a *= y.a
) as z
where z.ya is null
order by z.ya

-- this is ok, equivalent to query 1
select
z.a, z.b
from
(
select top 100 percent x.*, ya = Y.A
from x, y
where x.a *= y.a
) as z
where z.ya is null
order by z.a

/* me wondering why have to put TOP 100 PERCENT in the subquery above */




/*
drop table x
drop table y
*/
set nocount off