数据库左连接、右连接、全连接、交叉连接、自然连接

# 数据准备

create table t_group (
  group_id              number(4)      not null,
  group_name            varchar2(15)
);
create table t_person (
  person_id              number(4)     not null,
  person_name            varchar2(15),
  group_id               number(4)
);
insert into t_group(group_id, group_name) values(1, 'RESEARCH');
insert into t_group(group_id, group_name) values(2, 'SALES');
insert into t_person(person_id, person_name, group_id) values(1, 'zhangsan', 1);
insert into t_person(person_id, person_name, group_id) values(2, 'lisi', 1);
insert into t_person(person_id, person_name) values(3, 'wangwu');
commit;

# 交叉连接(cross join)
# 笛卡尔积(左表 x 右表)

select g.group_id, g.group_name, p.person_name 
from t_group g cross join t_person p;
等同于
select g.group_id, g.group_name, p.person_name 
from t_group g, t_person p;

# 自然连接(natural join)
# 自动对同名属性进行比较

select group_id, group_name, person_name 
from t_group g natural join t_person p;

# 左连接(left join == left outer join)
# 以左表为准，右表无数据时补空

select g.group_id, g.group_name, p.person_name 
from t_group g left join t_person p on g.group_id=p.group_id;

# 右连接(right join == right outer join)
# 以右表为准，左表无数据时补空

select g.group_id, g.group_name, p.person_name 
from t_group g right join t_person p on g.group_id=p.group_id;

# 全连接(full join == full outer join)
# 返回左右表的所有记录，任何一方无数据时补空

select g.group_id, g.group_name, p.person_name 
from t_group g full join t_person p on g.group_id=p.group_id;

# 内连接(join == inner join)
# 只返回相匹配的

select g.group_id, g.group_name, p.person_name 
from t_group g join t_person p on g.group_id=p.group_id;

# 等值连接(与内连接相同，只是 sql 格式不一样)

select g.group_id, g.group_name, p.person_name 
from t_group g, t_person p 
where g.group_id=p.group_id;

# 条件查询(泛指带 where 条件的查询)

select person_id, person_name, group_id from t_person where group_id is not null;