cross jion：将两张表合并起来，n个表使用n-1个cross join
语法：select * from 表1 cross join 表2 cross join 表3 cross join 表4 ... 表n where 条件；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46 | --将3张表合并，无序的没有意义
SQL> SELECT * FROM EMP T CROSS JOIN DEPT TT CROSS JOIN SALGRADE TTT;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC GRADE LOSAL HISAL
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- ------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980/12/17 800.00 20 10 ACCOUNTING NEW YORK 1 700 1200
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 10 ACCOUNTING NEW YORK 1 700 1200
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 10 ACCOUNTING NEW YORK 1 700 1200
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 10 ACCOUNTING NEW YORK 1 700 1200
7654 MARTIN SALESMAN 7698 1981/9/28 1250.10 1400.00 30 10 ACCOUNTING NEW YORK 1 700 1200
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 10 ACCOUNTING NEW YORK 1 700 1200
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 10 ACCOUNTING NEW YORK 1 700 1200
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 10 ACCOUNTING NEW YORK 1 700 1200
7839 KING PRESIDENT 1981/11/17 5000.23 10 10 ACCOUNTING NEW YORK 1 700 1200
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 10 ACCOUNTING NEW YORK 1 700 1200
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 10 ACCOUNTING NEW YORK 1 700 1200
7900 JAMES CLERK 7698 1981/12/3 950.55 30 10 ACCOUNTING NEW YORK 1 700 1200
7902 FORD ANALYST 7566 1981/12/3 3000.00 20 10 ACCOUNTING NEW YORK 1 700 1200
7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 ACCOUNTING NEW YORK 1 700 1200
7369 SMITH CLERK 7902 1980/12/17 800.00 20 10 ACCOUNTING NEW YORK 2 1201 1400
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 10 ACCOUNTING NEW YORK 2 1201 1400
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 10 ACCOUNTING NEW YORK 2 1201 1400
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 10 ACCOUNTING NEW YORK 2 1201 1400
7654 MARTIN SALESMAN 7698 1981/9/28 1250.10 1400.00 30 10 ACCOUNTING NEW YORK 2 1201 1400
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 10 ACCOUNTING NEW YORK 2 1201 1400
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC GRADE LOSAL HISAL
--将3张表合并，增加where条件
--这个sql语句是员工名单+城市+薪酬等级where后的结果
SQL> SELECT * FROM EMP T CROSS JOIN DEPT TT CROSS JOIN SALGRADE TTT where t.deptno = tt.deptno and sal between ttt.losal and ttt.hisal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC GRADE LOSAL HISAL
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- ------------- ---------- ---------- ----------
7839 KING PRESIDENT 1981/11/17 5000.23 10 10 ACCOUNTING NEW YORK 5 3001 9999
7902 FORD ANALYST 7566 1981/12/3 3000.00 20 20 RESEARCH DALLAS 4 2001 3000
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 20 RESEARCH DALLAS 4 2001 3000
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 20 RESEARCH DALLAS 4 2001 3000
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 30 SALES CHICAGO 4 2001 3000
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 10 ACCOUNTING NEW YORK 4 2001 3000
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 30 SALES CHICAGO 3 1401 2000
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 30 SALES CHICAGO 3 1401 2000
7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 ACCOUNTING NEW YORK 2 1201 1400
7654 MARTIN SALESMAN 7698 1981/9/28 1250.10 1400.00 30 30 SALES CHICAGO 2 1201 1400
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 30 SALES CHICAGO 2 1201 1400
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 20 RESEARCH DALLAS 1 700 1200
7900 JAMES CLERK 7698 1981/12/3 950.55 30 30 SALES CHICAGO 1 700 1200
7369 SMITH CLERK 7902 1980/12/17 800.00 20 20 RESEARCH DALLAS 1 700 1200
|