Oracle SQL查询，各种 联接

具体参考《21天学通Oracle》的第16章 SQL查询

 

DROP TABLE t_employee;
DROP TABLE t_salary;
DROP TABLE t_manager;

CREATE TABLE t_employee(
 ID NUMBER(11) NOT NULL,
 NAME VARCHAR2(20) NOT NULL
);
CREATE TABLE t_salary(
 ID NUMBER(11) NOT NULL,
 money NUMBER(10) NOT NULL,
 employee_id NUMBER(11)
);
CREATE TABLE t_manager(
 ID NUMBER(11) NOT NULL,
 NAME VARCHAR2(20) NOT NULL
);

INSERT INTO t_employee VALUES(1,'user1');
INSERT INTO t_salary values(1,100,1);
INSERT INTO t_employee VALUES(2,'user2');
INSERT INTO t_salary values(2,200,2);
INSERT INTO t_employee VALUES(3,'user3');
INSERT INTO t_salary values(3,300,3);
INSERT INTO t_employee VALUES(4,'user4');
INSERT INTO t_salary values(4,400,4);
INSERT INTO t_salary values(5,500,4);
INSERT INTO t_employee VALUES(5,'user5');
INSERT INTO t_manager VALUES(1,'user1');

--笛卡尔积
SELECT * FROM t_employee e, t_salary s;
--WHERE
SELECT * FROM t_employee e, t_salary s WHERE e.id = s.employee_id;
--GROUP BY, ORDER BY
SELECT E.NAME, SUM(S.MONEY) AMOUNT
  FROM T_EMPLOYEE E, T_SALARY S
 WHERE E.ID = S.EMPLOYEE_ID
 GROUP BY E.NAME
 HAVING SUM(S.MONEY)>250
 ORDER BY AMOUNT DESC;
--IN
SELECT * FROM t_employee e WHERE e.id IN (SELECT DISTINCT s.employee_id FROM t_salary s);
--EXISTS
SELECT * FROM t_employee e WHERE EXISTS (SELECT 1 FROM t_salary s WHERE e.id = s.employee_id);
--UNION
SELECT 0 ID, '请选择' NAME FROM DUAL
UNION
SELECT E.ID, E.NAME FROM T_EMPLOYEE E
UNION
SELECT M.ID, M.NAME FROM T_MANAGER M;
--UNION ALL
SELECT 0 ID, '请选择' NAME FROM DUAL
UNION ALL
SELECT E.ID, E.NAME FROM T_EMPLOYEE E
UNION ALL
SELECT M.ID, M.NAME FROM T_MANAGER M;
--INTERSECT
SELECT 0 ID, '请选择' NAME FROM DUAL
UNION
(SELECT E.ID, E.NAME FROM T_EMPLOYEE E
INTERSECT
SELECT M.ID, M.NAME FROM T_MANAGER M);
--MINUS
SELECT 0 ID, '请选择' NAME FROM DUAL
UNION
(SELECT E.ID, E.NAME FROM T_EMPLOYEE E
MINUS
SELECT M.ID, M.NAME FROM T_MANAGER M);
--自然联接
SELECT * FROM t_employee e NATURAL JOIN t_salary s NATURAL JOIN t_manager m;
--内联接
SELECT * FROM t_employee e INNER JOIN t_salary s ON e.id = s.employee_id;
--内联接 可以 完全被WHERE替代，而且最好只是用WHERE子句，就不用ON了
SELECT * FROM t_employee e, t_salary s WHERE e.id = s.employee_id;
--左外联接
SELECT * FROM t_employee e LEFT OUTER JOIN t_salary s ON e.id = s.employee_id; 
--左外联接，+形式，有+的一侧，表示该表为附表，另一个表为基表
SELECT * FROM t_employee e, t_salary s WHERE e.id = s.employee_id(+); 
--右外联接
SELECT * FROM t_employee e RIGHT OUTER JOIN t_salary s ON e.id = s.employee_id; 
--右外联接，+形式
SELECT * FROM t_employee e, t_salary s WHERE e.id(+) = s.employee_id; 
--完全外联接，执行过程是：先执行左外联接，再执行右外联接，最后进行UNION操作。开销很大，尽量避免使用。
SELECT * FROM t_employee e FULL JOIN t_salary s ON e.id = s.employee_id; 
--完全外联结 等价于
SELECT * FROM t_employee e LEFT OUTER JOIN t_salary s ON e.id = s.employee_id
UNION 
SELECT * FROM t_employee e RIGHT OUTER JOIN t_salary s ON e.id = s.employee_id;

 connect by 层次化查询，用云树形数据结构

 oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是：

select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>
;

<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。
<Conditional-2>：查询结果重起始根结点的限定条件。
<Conditional-3>：连接条件