左连接 右连接 內连接 测试

create table wfq1
(namechar(10),
idint)

create table wfq2
(namechar(10),
idint,
ageint
)

create table wfq3
(namechar(10),
id_eint,
education char(10)
)

create table wfq4
(namechar(10),
idint,
id_eint,
marriagechar(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_NUMnchar(10),
SLS_ORDR_LINE_NUMnchar(10),
DLVRY_NUMnchar(10),
DLVRY_ITEM_NUMnchar(10)
)

create table aa2
(
SLS_ORDR_NUMnchar(10),
SLS_ORDR_LINE_NUMnchar(10),
DLVRY_NUMnchar(10),
DLVRY_ITEM_NUMnchar(10)
)
create table aa3
(
SLS_ORDR_NUMnchar(10),
SLS_ORDR_LINE_NUMnchar(10),
DLVRY_NUMnchar(10),
DLVRY_ITEM_NUMnchar(10),
CARR_PICK_UP_DTnchar(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