几种连接方式

-- 创建两张实体表来演示
create table SYS_USER
(
  USERID        VARCHAR2(32) not null,
  CREATEDATE    DATE default sysdate, 
  USERNAME      VARCHAR2(32) 
)

alter table SYS_USER
  add constraint PK_USER primary key (USERID)
  using index
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
 
  create table SYS_ROLE_USER
  (
    USERID VARCHAR2(32) not null,
    ROLEID VARCHAR2(32) not null
  )
 
  alter table SYS_ROLE_USER
    add constraint PK_ROLE_USER primary key (USERID, ROLEID); 
  alter table SYS_ROLE_USER
    add constraint FK_USER_USERID foreign key (USERID)
    references SYS_USER (USERID) on delete cascade;
 
-- 内连接（两种等价)
select u.userid, u.username,r.roleid from sys_user u
 inner join sys_role_user r on u.userid = r.userid ;

select u.userid, u.username,r.roleid from sys_user u,Sys_Role_User r
where u.userid = r.userid;

select u.userid, u.username,r.roleid from sys_user u
 cross join sys_role_user r where u.userid = r.userid ;

-- 外连接(左外连接、右外连接、全外连接)
select u.userid,u.username,r.roleid from sys_user u
left outer join sys_role_user r on u.userid = r.userid order by r.roleid;

select u.userid,u.username,r.roleid from sys_user u
right outer join sys_role_user r on u.userid = r.userid order by r.roleid;

select u.userid,u.username,r.roleid from sys_user u
full outer join sys_role_user r on u.userid = r.userid order by r.roleid;

-- 笛卡尔积
select u.userid,u.username,r.roleid from sys_user u cross join sys_role_user r order by r.roleid;
select u.userid,u.username,r.roleid from sys_user u,sys_role_user r;

参考:http://www.cnblogs.com/youzhangjin/archive/2009/05/22/1486982.html