Oracle一对一与左关联

-- 一对一
-- 身份证表
CREATE TABLE idcard (
   idcard_num CHAR(18) PRIMARY KEY,
   name VARCHAR2(20),
   address VARCHAR2(100),
   minzu VARCHAR2(20)
);

-- 学生表
CREATE TABLE stu(
   sno VARCHAR2(10) PRIMARY KEY,
   name VARCHAR2(20),
   idcard_num CHAR(18)
);

INSERT INTO idcard(idcard_num, name, address, minzu)
VALUES
('123456789012345678','小芳','珠海南方','汉族');
INSERT INTO idcard(idcard_num, name, address, minzu)
VALUES
('123456789087654321','小军','珠海东方','傣族');

commit

select * from idcard

insert into stu(sno, name, idcard_num)
values
('001','小芳','123456789012345678');

insert into stu(sno, name, idcard_num)
values
('002','小军','123456789087654321');
commit

select * from idcard;
select * from stu;
update stu set name='大芳' where sno='001'
commit

select sno,t1.name,t1.idcard_num,address 
 from stu t1,idcard t2  
 where t1.idcard_num = t2.idcard_num 
 
 select * from stu
 -- 故意修改身份证号码，让它关联不上
 update stu set idcard_num='123456789012345677' 
 where idcard_num='123456789012345678'
 commit;
 -- 本质上是一个内关联
 -- 关联不上的数据，不显示
 select sno,t1.name,t1.idcard_num,address 
 from stu t1,idcard t2  
 where t1.idcard_num = t2.idcard_num 
 
 ----------
 -- 左关联left join
 -- （以左为主，左边关联不上的数据，继续显示）
  select sno,t1.name,t1.idcard_num,address 
 from stu t1,idcard t2  
 where t1.idcard_num = t2.idcard_num(+)
 --传统的写法
 
 select * from stu t1
 left join idcard t2
 on 
   t1.idcard_num = t2.idcard_num