select a.NAME,a.ID,b.DEP from 表A a,表B b where a.ID=b.ID
这样结果是 NAME ID DEP A 1 经理 B 2 顾问关键是下面那2个
select a.name,a.id,(case dep when null then '' end ) dep from 表A a left join 表b b on a.id=b.id
左外连接: select A.NAME,A.ID,B.DEP from A left join B on A.ID=B.id
--用left joinselect a.NAME,a.ID,b.DEP from 表A a left join 表B b on a.ID=b.ID
select a.NAME,a.ID,b.DEP from 表A a,表B b where a.ID *=b.ID
select 表A.* , isnull(表B.DEP,'') from 表A left join 表B on 表A.ID = 表B.id
if object_id('pubs..表A') is not null drop table 表A gocreate table 表A ( NAME varchar(10), ID varchar(10) )insert into 表A(NAME,ID) values('A','1') insert into 表A(NAME,ID) values('B','2') insert into 表A(NAME,ID) values('C','3') insert into 表A(NAME,ID) values('D','4')if object_id('pubs..表B') is not null drop table 表B gocreate table 表B ( ID varchar(10), DEP varchar(10) ) insert into 表B(ID,DEP) values('1','经理') insert into 表B(ID,DEP) values('2','顾问')select 表A.* , isnull(表B.DEP,'') as DEP from 表A left join 表B on 表A.ID = 表B.IDdrop table 表A,表BNAME ID DEP ---------- ---------- ---------- A 1 经理 B 2 顾问 C 3 D 4 (所影响的行数为 4 行)
NAME ID DEP
A 1 经理
B 2 顾问关键是下面那2个
select A.NAME,A.ID,B.DEP
from A left join B on A.ID=B.id
from 表A a
left join 表B b on a.ID=b.ID
left join
表B on 表A.ID = 表B.id
drop table 表A
gocreate table 表A
(
NAME varchar(10),
ID varchar(10)
)insert into 表A(NAME,ID) values('A','1')
insert into 表A(NAME,ID) values('B','2')
insert into 表A(NAME,ID) values('C','3')
insert into 表A(NAME,ID) values('D','4')if object_id('pubs..表B') is not null
drop table 表B
gocreate table 表B
(
ID varchar(10),
DEP varchar(10)
)
insert into 表B(ID,DEP) values('1','经理')
insert into 表B(ID,DEP) values('2','顾问')select 表A.* , isnull(表B.DEP,'') as DEP from 表A
left join 表B on 表A.ID = 表B.IDdrop table 表A,表BNAME ID DEP
---------- ---------- ----------
A 1 经理
B 2 顾问
C 3
D 4 (所影响的行数为 4 行)