--用戶表
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;