34,590
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: [tba]
if object_id('[tba]') is not null drop table [tba]
create table [tba] (col1 int)
insert into [tba]
select 1 union all
select 2 union all
select 3
--> 测试数据: [tbb]
if object_id('[tbb]') is not null drop table [tbb]
create table [tbb] (col1 int,col2 varchar(1))
insert into [tbb]
select 1,'a' union all
select 2,'a' union all
select 3,'b'
--开始查询
select * from [tba] a left join [tbb] b on (a.col1=b.col1 and b.col2='a')
/*
col1 col1 col2
----------- ----------- ----
1 1 a
2 2 a
3 NULL NULL
(3 行受影响)
*/
select * from [tba] a left join [tbb] b on (a.col1=b.col1) where b.col2='a'
/*
col1 col1 col2
----------- ----------- ----
1 1 a
2 2 a
(2 行受影响)
*/
--结束查询
drop table [tba],[tbb]
select * from [tba] a left join [tbb] b on (a.col1=b.col1 and b.col2='a')
--相当于
select * from [tba] a left join (select * from [tbb] where b.col2='a') b on a.col1=b.col1