join查询

6.3 Join Query

-- 初始化表create table loc (id int primary key, name varchar2(20));create table person (name varchar2(20), locid references loc);-- 初始化数据insert into gp values (1, '和平', '万岁');insert into gp values (2, '自由', '很好');insert into gp values (3, '民主', '爱国');insert into gp values (4, '敬业', '友善');insert into loc values (11, '广东');insert into loc values (22, '山东');insert into loc values (33, '湖南');insert into loc values (44, '江西');insert into person values ('王新炜', 33);insert into person values ('吴英平', 33);insert into person values ('杜志海', 44);insert into person values ('范锐', 11);commit;select * from person;select * from loc;-- 查询就是一个逐步过滤的过程-- 叉乘，内部杂交，虚拟表，16条数据select * from person, loc;-- 过滤掉不符合条件的数据。即完成一次外连接。-- 即：增一表，加一条件。select * from person, loc where person.locid = loc.id;-- 其他条件，即在上面的基础上继续过滤select * from person, loc where person.locid = loc.id and person.name like '王%';-- 增加另一个表create table gp (id int primary key, name varchar2(20), logo varchar2(10)); alter table person add  (gpid int references gp);-- 修改每个人的 group-- update ..select * from gp;select * from loc;select * from person;update loc set name='中华人民共和国湖南省' where id=33;commit;select * from person p, loc l, gp g;-- 将不符合的排除掉select * from person p, loc l, gp g  where p.locid = l.id    and p.gpid = g.id    -- and   l.name = '江西'    and g.name='和平'    and p.name like '王%';-- -- oracle 写法，非标准select * from person p, loc l, gp g  where p.locid = l.id and p.gpid = g.id;-- ansi sql 写法，join 默认是 inner joinselect * from person p   join loc l on p.locid = l.id   join gp g on p.gpid = g.id;-- 增加一个没有地区的人insert into person values ('黄秀', null, null);commit;-- 显示所有人，并将其地区信息查询出来。-- 需要使用外连接，将即使不匹配的人也查询出来-- oracle 写法select * from person, loc  where person.locid = loc.id(+);-- 标准写法select * from person p  left outer join loc l on p.locid = l.id;