Oracle 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;--WHERESELECT * FROM t_employee e, t_salary s WHERE e.id = s.employee_id;--GROUP BY, ORDER BYSELECT 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;--INSELECT * FROM t_employee e WHERE e.id IN (SELECT DISTINCT s.employee_id FROM t_salary s);--EXISTSSELECT * FROM t_employee e WHERE EXISTS (SELECT 1 FROM t_salary s WHERE e.id = s.employee_id);--UNIONSELECT 0 ID, '请选择' NAME FROM DUALUNIONSELECT E.ID, E.NAME FROM T_EMPLOYEE EUNIONSELECT M.ID, M.NAME FROM T_MANAGER M;--UNION ALLSELECT 0 ID, '请选择' NAME FROM DUALUNION ALLSELECT E.ID, E.NAME FROM T_EMPLOYEE EUNION ALLSELECT M.ID, M.NAME FROM T_MANAGER M;--INTERSECTSELECT 0 ID, '请选择' NAME FROM DUALUNION(SELECT E.ID, E.NAME FROM T_EMPLOYEE EINTERSECTSELECT M.ID, M.NAME FROM T_MANAGER M);--MINUSSELECT 0 ID, '请选择' NAME FROM DUALUNION(SELECT E.ID, E.NAME FROM T_EMPLOYEE EMINUSSELECT 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_idUNION 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>：连接条件
?
