--用户表
CREATE TABLE t_user
(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(32)
);
INSERT INTO t_user(ID,NAME) VALUES ('1','小赵');
INSERT INTO t_user(ID,NAME) VALUES ('2','小钱');
INSERT INTO t_user(ID,NAME) VALUES ('3','小孙');
INSERT INTO t_user(ID,NAME) VALUES ('4','小李');
INSERT INTO t_user(ID,NAME) VALUES ('5','小周');
INSERT INTO t_user(ID,NAME) VALUES ('6','小吴');
INSERT INTO t_user(ID,NAME) VALUES ('7','小郑');
INSERT INTO t_user(ID,NAME) VALUES ('8','小王');
--角色表
CREATE TABLE t_role
(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(32)
);
INSERT INTO t_role(ID,NAME) VALUES ('1','赵燕');
INSERT INTO t_role(ID,NAME) VALUES ('2','钱颖');
INSERT INTO t_role(ID,NAME) VALUES ('3','孙丽');
INSERT INTO t_role(ID,NAME) VALUES ('4','李平');
SELECT * FROM t_user;
SELECT * FROM t_role;
-- 自然连接
SELECT t1.id, t1.name, t2.id,t2.name FROM t_user t1, t_role t2;
--相等连接
SELECT t1.id, t1.name, t2.id,t2.name FROM t_user t1, t_role t2 WHERE t1.id=t2.id(+);
--外连接(是由加号所在另一侧的表,链接到加号所在的一侧)
SELECT t1.id, t1.name, t2.id,t2.name FROM t_user t1, t_role t2 WHERE t2.id(+)=t1.id AND t2.id(+)=4;