22,302
社区成员




--2条执行结果都一样!!!
--个人推荐 如果不是多表联系的条件放在where 里面
select A.a B.b from A join B on A.a = B.b where A.b=1 and A.c=2
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([a] int,[b] int)
insert [ta]
select 1,1 union all
select 2,2 union all
select 3,3 union all
select null,4
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int,[b] int)
insert [tb]
select 1,1 union all
select 2,null
go
--select * from [ta]
--select * from [tb]
select * from ta a left join tb b on a.a=b.a and b.b is not null
/*
a b a b
----------- ----------- ----------- -----------
1 1 1 1
2 2 NULL NULL
3 3 NULL NULL
NULL 4 NULL NULL
(4 行受影响)
*/
select * from ta a left join tb b on a.a=b.a where b.b is not null
/*
a b a b
----------- ----------- ----------- -----------
1 1 1 1
(1 行受影响)
*/
select * from benz2
1 1080001 jim cim M
2 1080002 tig cim M
3 1080003 ggg sr F
select * from benz3
1 1080001 jim cim M 25
2 1080002 tig cim M 26
3 1080005 fme cim F 26
以上是我的測試數據。
create table benz2(id varchar(7)not null,name varchar(10),dep varchar(10),sex char(1))
create table benz3(id varchar(7),name varchar(10),dep varchar(10),sex char(1),age char(2))
insert into benz3(id,name,dep,sex,age) values('1080005','fme','cim','F','26')
select benz2.NAME,benz3.dep from benz2 join benz3 on benz2.id = benz3.id and benz2.sex='M'
select benz2.NAME,benz3.dep from benz2 join benz3 on benz2.id = benz3.id where benz2.sex='M'
結果都是:
Name DEP
1 jim cim
2 tig cim