左连接 右连接 內连接 测试

 

create table wfq1
(name char(10),
 id  int)

create table wfq2
(name char(10),
 id  int,
 age int
)

 

create table wfq3
(name char(10),
 id_e int,
 education char(10)
)

create table wfq4
(name char(10),
 id  int,
 id_e int,
 marriage char(3)
)

insert into wfq1
values ('lt2',1988)

insert into wfq2
values ('fp',1987,24)

insert into wfq3
values ('lt2',30,'bs')

insert into wfq4
values('lt2',1988,'NO')

select * from wfq1 a
inner join wfq2 b
on a.name=b.name
and a.id=b.id
left join wfq3 c
on a.name=c.name
left join wfq4 d
on a.name=d.name
and c.id_e=d.id_e

select * from wfq3

select * from wfq4

update wfq4
set id_e  =30

 

create table aa1
(
SLS_ORDR_NUM nchar(10),
SLS_ORDR_LINE_NUM nchar(10),
DLVRY_NUM  nchar(10),
DLVRY_ITEM_NUM nchar(10)
)

create table aa2
(
SLS_ORDR_NUM nchar(10),
SLS_ORDR_LINE_NUM nchar(10),
DLVRY_NUM  nchar(10),
DLVRY_ITEM_NUM nchar(10)
)
create table aa3
(
SLS_ORDR_NUM nchar(10),
SLS_ORDR_LINE_NUM nchar(10),
DLVRY_NUM  nchar(10),
DLVRY_ITEM_NUM nchar(10),
CARR_PICK_UP_DT nchar(10)
)

insert into aa1 values (111,01,11,11)
insert into aa1 values (222,01,22,22)
insert into aa1 values (333,01,33,33)
insert into aa1 values (444,01,44,44)

insert into aa2 values (111,01,111,111)
insert into aa2 values (111,01,111,112)
insert into aa2 values (222,01,222,222)
insert into aa2 values (222,01,222,223)

insert into aa3 values (111,01,111,111,'wfq')
insert into aa3 values (222,01,222,222,'www')
insert into aa3 values (111,01,111,112,'wfq')
insert into aa3 values (222,01,222,223,'www')
insert into aa3 values (333,01,333,333,'wfq')
insert into aa3 values (333,01,333,332,'www')

select * from aa1
select * from aa2
select * from aa3

select * from aa1
left join aa2
on aa1.sls_ordr_num=aa2.sls_ordr_num
and aa1.SLS_ORDR_LINE_NUM=aa2.SLS_ORDR_LINE_NUM
left join aa3
on aa1.sls_ordr_num=aa3.sls_ordr_num
and aa1.SLS_ORDR_LINE_NUM=aa3.SLS_ORDR_LINE_NUM
and aa2.DLVRY_NUM=aa3.DLVRY_NUM
and aa2.DLVRY_ITEM_NUM=aa3.DLVRY_ITEM_NUM