select kot.kotcd,kot.hincd,kot.jn ,fzkotcd from kot left outer join fzkot on kot.kotcd = fzkot.kotcd and kot.hincd=fzkot.hincd
where fzkotcd is null
结果:
kotcd hincd jn fzkotcd
kot003 hin001 30 NULL
kot001 hin002 10 NULL
kot002 hin002 20 NULL
select kotcd,hincd,jn,fzkotcd from
(select A.kotcd,A.hincd,A.jn,B.fzkotcd from kot A,fzkot B where A.kotcd*=B.kotcd and A.hincd*=B.hincd ) C
where C.fzkotcd is null
结果:
kotcd hincd jn fzkotcd
kot001 hin001 10 NULL
kot002 hin001 20 NULL
kot003 hin001 30 NULL
kot001 hin002 10 NULL
kot002 hin002 20 NULL
kot003 hin002 30 NULL
kot004 hin002 40 NULL
where fzkotcd is null
结果:
kotcd hincd jn fzkotcd
kot003 hin001 30 NULL
kot001 hin002 10 NULL
kot002 hin002 20 NULL
select kotcd,hincd,jn,fzkotcd from
(select A.kotcd,A.hincd,A.jn,B.fzkotcd from kot A,fzkot B where A.kotcd*=B.kotcd and A.hincd*=B.hincd ) C
where C.fzkotcd is null
结果:
kotcd hincd jn fzkotcd
kot001 hin001 10 NULL
kot002 hin001 20 NULL
kot003 hin001 30 NULL
kot001 hin002 10 NULL
kot002 hin002 20 NULL
kot003 hin002 30 NULL
kot004 hin002 40 NULL
第二句相当于
select kot.kotcd,kot.hincd,kot.jn ,fzkotcd from kot join fzkot on kot.kotcd = fzkot.kotcd and kot.hincd=fzkot.hincd
where fzkotcd is null
结果为:
kot001 hin001 10 fz001
kot002 hin001 20 fz001
kot003 hin001 30 NULL
kot001 hin002 10 NULL
kot002 hin002 20 NULL
kot003 hin002 30 fz002
kot004 hin002 40 fz002
加上where fzkotcd is null,结果就变成:
kotcd hincd jn fzkotcd
kot001 hin001 10 NULL
kot002 hin001 20 NULL
kot003 hin001 30 NULL
kot001 hin002 10 NULL
kot002 hin002 20 NULL
kot003 hin002 30 NULL
kot004 hin002 40 NULL
奇怪
在早期的 Microsoft® SQL Server™ 2000 版本中,使用 *= 和 =* 在 WHERE 子句中指定左、右外部联接条件。有时,该语法会导致有多种解释的不明确查询
====================================瞻仰一下
建议楼主换一种写法比较好