oracle 表连接

　 建表：

create table STU
(
  id   NUMBER(3),
  name VARCHAR2(10)
)

create table EXAM
(
  eid        INTEGER not null,
  stuid      INTEGER not null,
  coursename VARCHAR2(20) not null,
  score      FLOAT
)

insert into stu (ID, NAME)
values (3, ‘kity‘);

insert into stu (ID, NAME)
values (2, ‘tom‘);

insert into stu (ID, NAME)
values (1, ‘jack‘);

insert into stu (ID, NAME)
values (4, ‘nono‘);

insert into exam (ID, GRADE)
values (11, 89);

insert into exam (ID, GRADE)
values (2, 76);

insert into exam (ID, GRADE)
values (1, 56);

select stu.id, exam.id, stu.name, exam.grade from stu left join exam on stu.id = exam.id;    --左连接
select stu.id, exam.id, stu.name, exam.grade from stu,exam where stu.id = exam.id(+);        --左链接
select stu.id, exam.id, stu.name, exam.grade from stu,exam where stu.id(+) = exam.id;        --右链接

select stu.id, exam.id, stu.name, exam.grade from stu,exam where stu.id = exam.id(+);        --左链接
-- "+" 号在右侧，则就是左连接，查询出来的结果集就以stu的总结果数为准，而exam
--中如果没有与stu关联上，则以空 补足，具体可以看sql语句的执行结果的截图
select stu.id, exam.id, stu.name, exam.grade from stu,exam where stu.id(+) = exam.id;        --右链接
-- "+" 号在左侧，则是右连接，同上。

　 左连接：

　 右连接：

oracle 表连接

标签：