-- 准备数据
CREATE TABLE t01( t_id NUMBER, t_no NUMBER);CREATE TABLE t02( t_id NUMBER, t_no NUMBER);INSERT INTO t01 VALUES(1, 3);INSERT INTO t01 VALUES(2, 1);INSERT INTO t01 VALUES(3, 0);INSERT INTO t02 VALUES(1, 1);INSERT INTO t02 VALUES(4, 4);INSERT INTO t02 VALUES(5, 5);-- 内连接:就是oracle的等于连接SELECT t01.t_id, t02.t_id FROM t01, t02 WHERE t01.t_id = t02.t_id;SELECT t01.t_id ,t02.t_id FROM t01 INNER JOIN t02 ON t01.t_id = t02.t_id;-- 左外连接:左连接的加号在右边,结果是匹配到的记录 + 左表中没有匹配的记录SELECT t01.t_id, t02.t_id FROM t01, t02 WHERE t01.t_id = t02.t_id(+);SELECT t01.t_id, t02.t_id FROM t01 LEFT OUTER JOIN t02 ON t01.t_id = t02.t_id;-- 右外连接:右连接的加号在左边,结果是匹配到的记录 + 右表中没有匹配的记录SELECT t01.t_id, t02.t_id FROM t01, t02 WHERE t01.t_id(+) = t02.t_id;SELECT t01.t_id, t02.t_id FROM t01 RIGHT OUTER JOIN t02 ON t01.t_id = t02.t_id;-- 交叉连接:笛卡尔积,左表中的每一条记录与右表中的记录一条条对应SELECT t01.t_id, t02.t_id FROM t01, t02;SELECT t01.t_id, t02.t_id FROM t01 CROSS JOIN t02;-- 不等连接:>、<、!=或者(<>),左表中的每一条记录与右表中的记录一条条比较SELECT t01.t_id, t02.t_id FROM t01, t02 WHERE t01.t_id > t02.t_id;SELECT t01.t_id, t02.t_id FROM t01, t02 WHERE t01.t_id < t02.t_id;SELECT t01.t_id, t02.t_id FROM t01, t02 WHERE t01.t_id != t02.t_id;-- 自连接SELECT t01.t_id, t01.t_no FROM t01 t1 WHERE t01.t_id = t1.t_no;-- 上面这种是错误的写法,下面这样写是对的SELECT t01.t_id, t01.t_no FROM t01, t01 t1 WHERE t01.t_id = t1.t_no;SELECT t1.t_id, t1.t_no FROM t01 t2, t01 t1 WHERE t1.t_id = t2.t_no;
上面代码的运行结果就不放在这里,要看结果直接把代码复制运行下就好了,之所以总结下,因为我老是把左右连接的加号放错位置,所以加深下记忆。上面都写了oracle中的sql以及sql1999的sql,一般做oracle数据库开发的都喜欢写oracle语法的sql,另一种sql可以用来辅助理解