Oracle sql连接

inner-join                    left-outer-join                 right-outer-join                 full-outer-join                          cross-join

演示上面的情况，建立departments 和employees 表

CREATE TABLE departments (
  department_id   NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
  department_name VARCHAR2(14),
  location        VARCHAR2(13)
);
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;

CREATE TABLE employees (
  employee_id   NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
  employee_name VARCHAR2(10),
  job           VARCHAR2(9),
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);

INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

下面分别用SQL 标准和ORACLE SQL的写法演示上面的5中情况

   

Departments表                                             employees表

case1:inner-join:查询部门号大于等于30的部门名和雇员名

An INNER JOIN combines data from two tables where there is a match on the joining column(s) in both tables.

SELECT d.department_name, e.employee_name     FROM departments d JOIN employees e ON d.department_id = e.department_id    WHERE d.department_id >= 30 ORDER BY d.department_name;

SELECT d.department_name, e.employee_name     FROM departments d, employees e    WHERE d.department_id = e.department_id AND d.department_id >= 30 ORDER BY d.department_name;

DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- SALES           ALLEN SALES           WARD SALES           JAMES SALES           BLAKE SALES           TURNER SALES           MARTIN 6 rows selected.

case2:left-outer-join

A LEFT [OUTER] JOIN returns all valid rows from the table on the left side of the JOIN keyword, along with the values from the table on the right side, or NULLs if a matching row doesn't exist.

SELECT d.department_name, e.employee_name     FROM    departments d          LEFT OUTER JOIN             employees e          ON d.department_id = e.department_id AND salary >= 2000      --條件放在這裡    WHERE d.department_id >= 30 ORDER BY d.department_name, e.employee_name;

SELECT d.department_name, e.employee_name     FROM departments d, employees e    WHERE     d.department_id = e.department_id(+)          AND d.department_id >= 30          AND e.salary(+) >= 2000     --注意这里的加号，用于处理salary为空的情况 ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N --------------          ---------- OPERATIONS SALES                  BLAKE 这里OPERATIONS的部门是40，因为没有雇员，所以salary为空，如果不写成e.salary(+),那么就无法输出部门OPERATIONS部门。这种写法通常用于处理空值

case3:right-outer-join

The RIGHT [OUTER] JOIN is the opposite of the LEFT [OUTER] JOIN. It returns all valid rows from the table on the right side of the JOIN keyword, along with the values from the table on the left side, or NULLs if a matching row doesn't exist.

SELECT d.department_name,        e.employee_name     FROM   departments d        RIGHT OUTER JOIN employees e ON d.department_id = e.department_id WHERE  d.department_id >= 30 ORDER BY d.department_name, e.employee_name;

SELECT d.department_name, e.employee_name     FROM departments d, employees e    WHERE d.department_id(+) = e.department_id AND d.department_id >= 30 ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- SALES           ALLEN SALES           BLAKE SALES           JAMES SALES           MARTIN SALES           TURNER SALES           WARD 6 rows selected.

case4:full-outer-join

A FULL [OUTER] JOIN combines all the rows from the tables on the left and right sides of the join. If there is a conventional match it is made. If either side has missing data, it is replaced by NULLs, rather than throwing the row away.

INSERT INTO employees VALUES (8888,'JONES','DBA',null,to_date('02-1-1982','dd-mm-yyyy'),1300,NULL,NULL); COMMIT; SELECT d.department_name,        e.employee_name     FROM   departments d        FULL OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_name, e.employee_name;

SELECT d.department_name,        e.employee_name     FROM   departments d, employees e where  d.department_id (+)= e.department_id union all SELECT d.department_name,        e.employee_name     FROM   departments d, employees e where  d.department_id = e.department_id(+) and e.employee_name is  null order by 1,2 ; DELETE FROM employees WHERE employee_id = 8888; COMMIT;

DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- ACCOUNTING     CLARK ACCOUNTING     KING ACCOUNTING     MILLER OPERATIONS RESEARCH       ADAMS RESEARCH       FORD RESEARCH       JONES RESEARCH       SCOTT RESEARCH       SMITH SALES           ALLEN SALES           BLAKE SALES           JAMES SALES           MARTIN SALES           TURNER SALES           WARD                    JONES 16 rows selected.

case5:cross-join就是笛卡尔连接 d的每一行连接e的全部，结果集是m（d的行数）*n(e的行数)

SELECT d.department_name, e.employee_name     FROM departments d CROSS JOIN employees e ORDER BY d.department_name, e.employee_name;

SELECT d.department_name, e.employee_name     FROM departments d, employees e ORDER BY d.department_name, e.employee_name;

结果是56行 4*14行

case6:nature-join自然连接，是inner-join的变体，不推荐用这种写法

SELECT d.department_name,        e.employee_name     FROM   departments d        NATURAL JOIN employees e ORDER BY d.department_name, e.employee_name;

没有non-ansi写法

DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- ACCOUNTING     CLARK ACCOUNTING     KING ACCOUNTING     MILLER RESEARCH       ADAMS RESEARCH       FORD RESEARCH       JONES RESEARCH       SCOTT RESEARCH       SMITH SALES           ALLEN SALES           BLAKE SALES           JAMES SALES           MARTIN SALES           TURNER SALES           WARD 14 rows selected.

参考：https://oracle-base.com/articles/misc/sql-for-beginners-joins