连接查询问题

Question

有两个表create table tb_sal(
EmpID varchar2(20) unique references tb_employee(EmpID),
Salary             NUMBER,
bonus NUMBER,
deduct NUMBER default 0
);
create table tb_Extrawork  (
   EmpID              varchar(20) references tb_employee(EmpID) not null,
   EwDate             DATE                           ,
   EwConut NUMBER not null,
   constraint PK_EXTRAWORK primary key (EmpID)
);
insert into tb_sal(empid,Salary,bonus,deduct) values('e1001',5000,1000,0);
insert into tb_sal(empid,Salary,bonus,deduct) values('e1002',5000,1000,0);
insert into tb_sal(empid,Salary,bonus,deduct) values('e1003',5000,1000,0);
insert into tb_sal(empid,Salary,bonus,deduct) values('e1004',5000,1000,0);
insert into tb_extrawork(EmpID,ewdate,ewconut) values('e1001',to_date('2011-10-01','yyyy-mm-dd'),5);
insert into tb_extrawork(EmpID,ewdate,ewconut) values('e1002',to_date('2011-10-01','yyyy-mm-dd'),5);
insert into tb_extrawork(EmpID,ewdate,ewconut) values('e1003',to_date('2011-10-01','yyyy-mm-dd'),5);其中我想查询效果如下 empid   Salary    bonus    deduct     ewdate   ewconut
e1004    5000     1000        0          0        0

Answer 1

select t1.empid, t1.Salary, t1.bonus, t1.deduct, t2.ewdate, t2.ewconut
from table tb_sal t1, tb_Extrawork t2
where t1.empid = t2.EmpID反正你的EmpID是主key，又是外键

Answer 2

全外连接
select * where tb_sal t1 full join tb_Extrawork t2 on t1.empid = t2.EmpID

Answer 3

不好意思，上边写的错了select t1.empid, t1.Salary, t1.bonus, t1.deduct, t2.ewdate, t2.ewconut
from tb_sal t1 full join tb_Extrawork t2
on t1.empid = t2.EmpID

Answer 4

不就是left join加nvl吗
select t1.empid, t1.Salary, t1.bonus, t1.deduct, nvl(t2.ewdate,0), nvl(t2.ewconut,0)
from table tb_sal t1
left join tb_Extrawork t2 on t1.empid = t2.EmpID
where t1.empid='e1004'
;