create table 表1 ( id int,column1 varchar(3) )create table 表2 ( id int,tablid int,column2 varchar(3) )insert into 表1 select 1,'abc'insert into 表2 select 1,1,'efg' union all select 2,1,'hij'select a.id,a.tablid,b.column1,a.column2 from 表2 a left join 表1 b on a.tablid=b.id ------------------------------------------ id tab1id column1 column2 1 1 abc efg 2 1 abc hij (所影响的行数为 2 行)
if exists(select 1 from sysobjects where id=object_id('table_a') and objectproperty(id,'isTable')=1) drop table table_aif exists(select 1 from sysobjects where id=object_id('table_b') and objectproperty(id,'isTable')=1) drop table table_b gocreate table table_a(id int not null ,column1 varchar(20) null,primary key(id))create table table_b(id int not null ,tab1id int not null ,column2 varchar(20) null,primary key(id)) goinsert into table_a select 1,'abc' insert into table_b select 1,1,'efg' union select 2,1,'hij' go select * from table_a /* id column1 1 abc */select * from table_b /* id tab1id column2 1 1 efg 2 1 hij */select b.id,b.tab1id,column1,column2 from table_a a join table_b b on a.id=b.tab1id /* id tab1id column1 column2 1 1 abc efg 2 1 abc hij */
(
id int,column1 varchar(3)
)create table 表2
(
id int,tablid int,column2 varchar(3)
)insert into 表1
select 1,'abc'insert into 表2
select 1,1,'efg' union all
select 2,1,'hij'select a.id,a.tablid,b.column1,a.column2 from 表2 a left join 表1 b on a.tablid=b.id
------------------------------------------
id tab1id column1 column2
1 1 abc efg
2 1 abc hij
(所影响的行数为 2 行)
drop table table_aif exists(select 1 from sysobjects where id=object_id('table_b') and objectproperty(id,'isTable')=1)
drop table table_b
gocreate table table_a(id int not null ,column1 varchar(20) null,primary key(id))create table table_b(id int not null ,tab1id int not null ,column2 varchar(20) null,primary key(id))
goinsert into table_a
select 1,'abc' insert into table_b
select 1,1,'efg' union
select 2,1,'hij' go
select * from table_a
/*
id column1
1 abc
*/select * from table_b
/*
id tab1id column2
1 1 efg
2 1 hij
*/select b.id,b.tab1id,column1,column2 from table_a a join table_b b on a.id=b.tab1id
/*
id tab1id column1 column2
1 1 abc efg
2 1 abc hij
*/