java 利用 flatmap 进行笛卡尔积 oracle笛卡尔积

转载

boyboy 2023-12-14 12:29:34

文章标签 SQL 表连接运算符 文章分类 Java 后端开发

多表连接

要从多张表中得到信息，就需要以一定的条件将多张表连接起来。

笛卡儿连接

多表连接主要分类：

等价连接
不等连接
外键连接
自连接

实验18：表的连接查询

笛卡儿（Cartesian）连接

笛卡儿积是把表中所有的记录作乘积操作，结果是第一张表的所有行和第二张表的所有行进行矩阵相乘，得到m*n行的结果。
而通常结果中可用的值有限。笛卡尔积出现的原因多种多样，通常是由于连接条件缺失造成的。

SQL> select ename , dname from emp,dept;

ENAME      DNAME
---------- --------------
SMITH      ACCOUNTING
ALLEN      ACCOUNTING
WARD       ACCOUNTING
JONES      ACCOUNTING
MARTIN     ACCOUNTING
BLAKE      ACCOUNTING
CLARK      ACCOUNTING
KING       ACCOUNTING
TURNER     ACCOUNTING
JAMES      ACCOUNTING
FORD       ACCOUNTING
MILLER     ACCOUNTING
SMITH      RESEARCH
ALLEN      RESEARCH
WARD       RESEARCH
JONES      RESEARCH
MARTIN     RESEARCH
BLAKE      RESEARCH
CLARK      RESEARCH
KING       RESEARCH
TURNER     RESEARCH
JAMES      RESEARCH
FORD       RESEARCH
MILLER     RESEARCH
SMITH      SALES
ALLEN      SALES
WARD       SALES
JONES      SALES
MARTIN     SALES
BLAKE      SALES
CLARK      SALES
KING       SALES
TURNER     SALES
JAMES      SALES
FORD       SALES
MILLER     SALES
SMITH      OPERATIONS
ALLEN      OPERATIONS
WARD       OPERATIONS
JONES      OPERATIONS
MARTIN     OPERATIONS
BLAKE      OPERATIONS
CLARK      OPERATIONS
KING       OPERATIONS
TURNER     OPERATIONS
JAMES      OPERATIONS
FORD       OPERATIONS
MILLER     OPERATIONS

已选择48行。

结果是每个员工在每个部门上了一次班，4*12=48行。
要避免笛卡尔连接要给你正确的连接条件。

等价连接

等价连接又称简单连接或内连接。就是当两个表的公共字段相等的时候把两个表连接在一起。公共字段是两个表中有相同含义的列。

等价连接的语法结构

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1=table2.column2;

-在WHERE 子句中写连接条件
-当多个表中有重名列时，必须在列的名字前加上表名作为前缀

SQL> select ename,dname from emp,dept where emp.deptno=dept.deptno;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
KING       ACCOUNTING
TURNER     SALES
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

已选择12行。

等价连接中的记录筛选

-多表连接中，记录筛选语句同样写在WHERE语句中，用逻辑AND和连接判断语句写在一起。

SQL> select ename,job,dname from emp,dept where emp.deptno=dept.deptno and job like '%MAN%';

ENAME      JOB       DNAME
---------- --------- --------------
ALLEN      SALESMAN SALES
WARD       SALESMAN SALES
JONES      MANAGER   RESEARCH
MARTIN     SALESMAN SALES
BLAKE      MANAGER   SALES
CLARK      MANAGER   ACCOUNTING
TURNER     SALESMAN SALES

已选择7行。

表的别名

–便于书写，将同名的表区分
–表别名长度不超过30个字符;
–表别名定义在FROM子句中；
–如果已经定义了表别名，那么只能使用表别名而不能使用原表名；
–表别名的有效范围只是当前语句。
–定义方式为表别名紧跟别名，用空格间隔。

SQL语句的书写顺序是：
–SELECT FROM WHERE ORDER BY
而实际的执行顺序是：
–FROM WHERE SELECT ORDER BY

SQL> select ename,dname from emp e ,dept d where e.deptno=d.deptno;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH

ENAME      DNAME
---------- --------------
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

已选择14行。

列的别名

为了区分相同列的名称，这是别名的本质。

SQL> select ename,dname,e.deptno,d.deptno from emp e ,dept d where e.deptno=d.deptno;

ENAME      DNAME              DEPTNO     DEPTNO
---------- -------------- ---------- ----------
SMITH      RESEARCH               20         20
ALLEN      SALES                  30         30
WARD       SALES                  30         30
JONES      RESEARCH               20         20
MARTIN     SALES                  30         30
BLAKE      SALES                  30         30
CLARK      ACCOUNTING             10         10
SCOTT      RESEARCH               20         20
KING       ACCOUNTING             10         10
TURNER     SALES                  30         30
ADAMS      RESEARCH               20         20

ENAME      DNAME              DEPTNO     DEPTNO
---------- -------------- ---------- ----------
JAMES      SALES                  30         30
FORD       RESEARCH               20         20
MILLER     ACCOUNTING             10         10

已选择14行。
两个列名都是deptno，无法区分。

通过列的别名区分
SQL> select ename,dname,e.deptno "员工表的部门编号",d.deptno "部门表的部门编号"
from emp e ,dept d
where e.deptno=d.deptno;

ENAME      DNAME          员工表的部门编号部门表的部门编号
---------- -------------- ---------------- ----------------
SMITH      RESEARCH                     20               20
ALLEN      SALES                        30               30
WARD       SALES                        30               30
JONES      RESEARCH                     20               20
MARTIN     SALES                        30               30
BLAKE      SALES                        30               30
CLARK      ACCOUNTING                   10               10
SCOTT      RESEARCH                     20               20
KING       ACCOUNTING                   10               10
TURNER     SALES                        30               30
ADAMS      RESEARCH                     20               20
JAMES      SALES                        30               30
FORD       RESEARCH                     20               20
MILLER     ACCOUNTING                   10               10

已选择14行。

不等连接

除了等号之外，在表连接语句中还可以使用其它的运算符。这种使用除等号之外运算符的连接语句被称为不等价连接。
使用不等价连接查询可以查询两个表中具有非等值关系的数据。操作符可以是比较运算符，也可以是between...and或者是in、like。

SQL> select ename,sal,grade from emp,salgrade
where sal between losal and hisal;

ENAME             SAL      GRADE
---------- ---------- ----------
SMITH             800          1
JAMES             950          1
ADAMS            1100          1
WARD             1250          2
MARTIN           1250          2
MILLER           1300          2
TURNER           1500          3
ALLEN            1600          3
CLARK            2450          4
BLAKE            2850          4
JONES            2975          4
SCOTT            3000          4
FORD             3002          5
KING             5000          5

已选择14行。

两个以上表连接

SQL> select a.ename,a.sal,b.dname,c.grade
from emp a,dept b,salgrade c
where a.deptno=b.deptno and a.sal between c.losal and c.hisal;

ENAME             SAL DNAME               GRADE
---------- ---------- -------------- ----------
SMITH             800 RESEARCH                1
JAMES             950 SALES                   1
ADAMS            1100 RESEARCH                1
WARD             1250 SALES                   2
MARTIN           1250 SALES                   2
MILLER           1300 ACCOUNTING              2
TURNER           1500 SALES                   3
ALLEN            1600 SALES                   3
CLARK            2450 ACCOUNTING              4
BLAKE            2850 SALES                   4
JONES            2975 RESEARCH                4
SCOTT            3000 RESEARCH                4
FORD             3002 RESEARCH                5
KING             5000 ACCOUNTING              5

已选择14行。

外连接

为了查找到所有记录，包括没有匹配的记录，需要用外连接语句来实现。
+号不能同时放在左右两边，只能出现在一边。

右外连接，如果左边的列所在表是缺乏表

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+)=table2.column;

左外连接，如果右边的列所在表是缺乏表

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column=table2.column(+);

SQL> select ename,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno；

ENAME      DNAME              DEPTNO
---------- -------------- ----------
SMITH      RESEARCH               20
ALLEN      SALES                  30
WARD       SALES                  30
JONES      RESEARCH               20
MARTIN     SALES                  30
BLAKE      SALES                  30
CLARK      ACCOUNTING             10
KING       ACCOUNTING             10
TURNER     SALES                  30
JAMES      SALES                  30
FORD       RESEARCH               20
MILLER     ACCOUNTING             10

已选择12行。

这条语句不会显示40号的部门，因为40号没有员工。

SQL> select ename,dname,dept.deptno from emp,dept where emp.deptno(+)=dept.deptno；

ENAME      DNAME              DEPTNO
---------- -------------- ----------
KING       ACCOUNTING             10
CLARK      ACCOUNTING             10
MILLER     ACCOUNTING             10
FORD       RESEARCH               20
SMITH      RESEARCH               20
JONES      RESEARCH               20
JAMES      SALES                  30
TURNER     SALES                  30
MARTIN     SALES                  30
WARD       SALES                  30
ALLEN      SALES                  30
BLAKE      SALES                  30
           OPERATIONS             40

已选择13行。

将没有员工的部门用NULL来匹配。

自连接

表的一列和同一个表的另一列作为连接的条件。

SQL> select w.ename "下级",m.ename "上级"
from emp w,emp m
where w.mgr=m.empno(+);

下级       上级
---------- ----------
SMITH      FORD
ALLEN      BLAKE
WARD       BLAKE
JONES      KING
MARTIN     BLAKE
BLAKE      KING
CLARK      KING
KING
TURNER     BLAKE
JAMES      BLAKE
FORD       JONES

下级上级
---------- ----------
MILLER CLARK

已选择12行。

"下级"和 "上级"为列的别名，区分相同的列。
w和m为表的别名，区分相同的表。
(+)为了将没有上级的人也显示。

实验19：sql99规则的表的连接操作

sql99：美国国家标准协会（ANSI）的SQL：1999标准的连接语法

语法：

CROSS JOIN：交叉连接，生成笛卡尔积

SQL> select ename,dname from emp cross join dept;

已选择48行。

NATURAL JOIN：自然连接

所有同名的列都作为等价条件。
同名的列的数据类型必须匹配。
列的名称前不能加表的前缀。

SQL> select ename,deptno,dname from emp natural join dept;

ENAME          DEPTNO DNAME
---------- ---------- --------------
SMITH              20 RESEARCH
ALLEN              30 SALES
WARD               30 SALES
JONES              20 RESEARCH
MARTIN             30 SALES
BLAKE              30 SALES
CLARK              10 ACCOUNTING
KING               10 ACCOUNTING
TURNER             30 SALES
JAMES              30 SALES
FORD               20 RESEARCH
MILLER             10 ACCOUNTING

已选择12行。

USING (column_name)：指定列的连接

当有多列同名，但想用其中某一列作为连接条件时使用。
USING子句和NATURAL JOIN不能在一条语句中同时书写。

SQL> select ename , deptno,dname from emp join dept using(deptno);

已选择12行。

JOIN table2 ON (table1.column_name= table2.column_name)：等价连接语句

SQL> SELECT e.ename,e.job,e.deptno,d.dname
FROM emp e JOIN dept d ON (e.deptno=d.deptno);

ENAME      JOB           DEPTNO DNAME
---------- --------- ---------- --------------
SMITH      CLERK             20 RESEARCH
ALLEN      SALESMAN          30 SALES
WARD       SALESMAN          30 SALES
JONES      MANAGER           20 RESEARCH
MARTIN     SALESMAN          30 SALES
BLAKE      MANAGER           30 SALES
CLARK      MANAGER           10 ACCOUNTING
KING       PRESIDENT         10 ACCOUNTING
TURNER     SALESMAN          30 SALES
JAMES      CLERK             30 SALES
FORD       ANALYST           20 RESEARCH
MILLER     CLERK             10 ACCOUNTING

已选择12行。

sql99的外键连接

[LEFT|RIGHT|FULL OUTER JOIN：左外连接|右外连接|全外连接

在LEFT OUTER JOIN中，会返回所有左边表中的行，即使在右边的表中没有可对应的列值。

SQL> select ename,dname,dept.deptno
from dept left outer join emp
on(dept.deptno=emp.deptno);

BLAKE SALES 30
OPERATIONS 40

已选择13行。

返回目录

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。