-- 创建两张实体表来演示
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