您的当前位置：首页正文

oracle多表查询

2023-11-09 来源：东饰资讯网

笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合。

-- 笛卡尔积 select * from emp, dept;

-- 使用公共字段，去掉笛卡尔积select * from emp, dept where emp.deptno = dept.deptno;

-- 查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名-- 在emp表中的MGR表示一个雇员的上级领导的编号，如果现在要想查询一个雇员的上级领导姓名，则需要用emp表与emp表进行自连接操作select e.ename, e.job, s.ename from emp s, emp e where s.empno = e.mgr; -- 查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名，雇员所在的部门名称select e.ename, e.job,s.ename mgr_name, d.dname from emp e, emp s, dept d where e.mgr = s.empno and e.deptno = d.deptno;-- 查询出每个雇员的姓名，工资，部门名称，工资在公司的等级及其领导的姓名，领导的工资，以及领导所对应的等级SELECT e.ename, e.sal, d.dname,decode(g.grade,5,'第一等工资',4,'第二等工资',3,'第三等工资',2,'第四等工资',1,'第五等工资')e_grade, s.ename mgr_name, s.sal mgr_sal,decode(g2.grade,5,'第一等工资',4,'第二等工资',3,'第三等工资',2,'第四等工资',1,'第五等工资') m_gradeFROM emp e,dept d,salgrade g,emp s,salgrade g2WHERE (e.deptno=d.deptno) AND (e.sal BETWEEN g.losal AND g.hisal)AND (s.empno=e.mgr) AND (s.sal BETWEEN g2.losal AND g2.hisal);

左右连接

-- 利用emp 和 dept 做一个连接查询，查询结果包括雇员编号，雇员姓名，部门编号，部门名称，部门所在位置select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e,dept dwhere e.deptno = d.deptno;

-- 通过观察不难发现，在以上结果中部门编号40没有出现,这是因为在进行普通连接时两边都有的才出现在结果中,在emp表中没有部门40,所以结果中也就没有40select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e, dept dwhere e.deptno(+) = d.deptno;

部门40就出现在结果中了.此时我们使用的是右连接。

(+)在=左边表示右连接 (+)在=右边表示左连接

左连接以左表为基准，右连接以右表为基准

-- 左连接以左表为基准，右连接以右表为基准select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e, dept dwhere e.deptno = d.deptno(+);

-- 查询雇员的编号，姓名，及其领导的编号，姓名select e.empno, e.ename, s.empno, s.enamefrom emp e, emp swhere e.mgr = s.empno;

-- 但我们经查询知道emp表中应该有14条记录,在以上结果中缺少了KING,因为KING是最高领导，所以他的领导编号为空，故没办法查询出来.为了显示出来，我们用左/右连接来实现select e.empno, e.ename, s.empno, s.enamefrom emp e, emp swhere e.mgr = s.empno(+);

技术分享

-- 左右连接的连接方式不是固定的，具体采用左连接还是右连接取决于基准条件在等式的左边还是右边，上面的左连接把基准条件放在右边，就变为了右连接select e.empno, e.ename, s.empno, s.enamefrom emp e, emp swhere s.empno(+) = e.mgr;

得到的同样是上图的结果只，这里就不重复贴图了。

SQL:1999SQL定义

语法格式

SELECT table1.column,table2.column

FROM table1 [CROSS JOIN table2]|

[NATURAL JOIN table2]|

[JOIN table2 USING(column_name)]|

[JOIN table2 ON(table1.column_name=table2.column_name)]|

[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name=table2.column_name)]

-- 交叉连接(CROSS JOIN):迪卡尔积运算select * from emp cross join dept;

select * from emp cross join dept where emp.empno = 7369;

技术分享

-- 自然连接 NATURAL JOINselect * from emp natural join dept;

技术分享

-- 根据结果可以发现，自然连接按公共字段相等进行连接，并且产生的结果会自动消除重复的列select * from emp e, dept dwhere e.deptno = d.deptno;

技术分享

-- USING(column_name) 用于指定两个表之间的连接字段select * from emp join dept using(deptno);

技术分享

-- ON(tab1.column_name=tab2.column_name) 用于指定两表的连接条件select * from emp join dept on emp.deptno = dept.deptno;

技术分享

-- LEFT[OUTER] JOIN 左连接 OUTER可有可无select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e left join dept don e.deptno = d.deptno;select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e left outer join dept don e.deptno = d.deptno;

技术分享

-- RIGHT JOIN 右连接select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e right join dept don e.deptno = d.deptno;

技术分享

-- INNER JOIN 取交集create table empbak as select * from emp where emp.empno in (7369, 7499, 7521, 7566, 7654, 7698);select e1.empno, e1.empno, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno from emp e1 inner join empbak e2 on e1.empno = e2.empno;

技术分享

-- FULL OUTER JOIN 取并集update empbak e set e.empno = 8369 where e.empno = 7369;update empbak e set e.empno = 8499 where e.empno = 7499;update empbak e set e.empno = 8521 where e.empno = 7521;select * from emp e full join empbak s on e.empno = s.empno;

技术分享

-- 对于没有匹配的记录，则会以null做为值select * from emp e full join empbak s on e.empno = s.empnowhere e.empno is not null and s.empno is not null;

技术分享

-- UNION 取并集 去掉重复记录select * from empunionselect * from empbak;

技术分享

-- UNION ALL 取并集 不去除重复记录select * from empunion allselect * from empbak;

技术分享

最下面的三条记录是重复记录，在上面的union连接中，多余的三条重复记录是被去掉的。

oracle多表查询

标签：select 多表查询左连接右连接

小编还为您整理了以下内容，可能对您也有帮助：

oracle查看所有表及各表行数

在Oracle数据库中，查看所有表及对应个表的行数，只用一个select语句查询table_name和num_rows两个字段即可。table_name是表名，num_rows代表表的行数。

具体如下：

1、查询数据库所有的表sql：select t.table_name,t.num_rows from all_tables t；

sql执行后的输出结果如下图：

2、查询当前用户表sql：select t.table_name,t.num_rows from user_tables t；

sql执行后输出结果如下图：

扩展资料：

分享一些ORACLE中，对所有表的查询sql：

1、查询所有表名：

select t.table_name from user_tables t;

2、查询所有字段名：

select t.column_name from user_col_comments t;

3、查询指定表的所有字段名：

select t.column_name from user_col_comments t where t.table_name = 'BIZ_DICT_XB';

4、查询指定表的所有字段名和字段说明：

select t.column_name, t.column_name from user_col_comments t where t.table_name = 'BIZ_DICT_XB';

5、查询所有表的表名和表说明：

select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name;

6、查询模糊表名的表名和表说明：

select t.table_name from user_tables t where t.table_name like 'BIZ_DICT%';

select t.table_name,f.comments from user_tables t inner join user_tab_comments f

on t.table_name = f.table_name where t.table_name like 'BIZ_DICT%';

7、查询表的数据条数、表名、中文表名

select a.num_rows, a.TABLE_NAME, b.COMMENTS from user_tables a,user_tab_comments b WHERE a.TABLE_NAME = b.TABLE_NAME order by TABLE_NAME;

参考资料来源：百度百科-Oracle PL/SQL宝典（第2版）

参考资料来源：百度百科-Oracle数据库编程经典300例

Oracle之查询详解

查询是数据的一个重要操作。用户发送查询请求，经编译软件变异成二进制文件供服务器查询，后返回查询结果集给用户，查询会产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正的存储，每次执行查询只是从数据表中提取数据，并按照表的形式显示出来。

SELECT <列名>

FGROM <表名>

[WHERE <查询条件表达式>]

[GROUP BY <分组表达式>]

[HAVING <分组查询表达式>]

[ORDER BY <排序的列名> [ASC或DESC]]

group by 用于对查询的结果分组统计，通过对group by后面的名字进行分组后输出结果。

group by后面还可以跟多列表示多列分组，在多列分组时放前面的优先分组。

group by 列名,列名

having 子句用于*分组显示结果，其只能和group by一起连用。在where中没有办法直接使用聚合函数，即sum avg等无法使用，所以引用了having，在having中可以使用这些函数。

order by 表示排序，后跟列名和排序方式。如果什么都不加默认为升序。ASC表示升序，DESC表示降序。

在Oracle中还可以设置多列排序

order by 列名1 升降,列名2 升降;

前面的为主要排序，后面的为次一级排序。

注：碰到自己与自己比较的情况下，不能用having，可以创建一个新列。

注：如果select语句同时包含group by,having,order by,按group by,having,order by排序

分组和聚合一起使用，目的是为了统计信息。

where是为了from服务的，只能跟真实的字段，用来筛选from子句中指定的操作所产生的行

group by 用来分组where子句的输出

having 用来从分组的结果中筛选行

order by用来对筛选的结果进行排序

（1）分组函数：max min avg sum count

max表示该列的最大值，min表示该列的最小值，avg表示该列的平均值，sum表示该列的和，count表示该列的行数。

注：分组函数(max、min、avg、count、sum)只能出现在选择列表中having子句、order by子句、不能出现在where子句和group by子句中。

（2）多表查询

多表查询是指两个和两个以上的表或者是视图的查询，在实际应用中，当查询单个表不能满足需求时，一般使用多表查询。如：显示sales部门位置和其员工的姓名，这种情况下需要使用到(dept表和emp表)。

多表查询的连接一般可以分为：内连接、左外连接、右外连接、全连接。

注：在使用多表查询的时候每个表可以设置别名，如果表指定了别名，那么语句中所有语句必须使用别名，而不能再使用实际表名。且在写属性的时候如果属性为其中一个表特有的属性则不需要写别名，如果是两个表都有则必须指定是哪一个表的哪个属性格式为：表名.属性名。

select 列名 from 表1 别名,表2 别名...

注：e是emp的别名，d是dept的别名。

但如果对表进行了操作则需要设置别名，如：查询每个部门中工资高于该部门平均工资的员工人数。在其中有一个avg表，这个表必须设置别名（提醒：如果仅有一个被修改的表，则可以不设置别名，但如果有多个表则必须设置别名）。

内连接

内连接通过使用比较运算符来使每个表的通用列中的值匹配来组成一个新表，即：把两个表中间共有的那些行拿出来进行连接，如果某些行不是两个表共有的，则不进行连接。

select

from 表1

inner join 表2

on 匹配条件

或

select

from 表1 表2

where匹配条件

左外连接

左外连接与内连接的区别是：设置左外连接的时候设置了主表和附表，主表在前，附表在后。内连接是将两个表匹配的地方输出出来，而左外连接则是主表全写，附表一一对应，附表有则加上，没有不写。

select

from 表1

left join 表2

on 匹配条件

右外连接

右外连接和左外连接基本相同只是右外连接的主表写在后边。

select

from 表1

right join 表2

on 匹配条件

全连接

全连接是在等值连接的基础上将左表和右表的未匹配数据都加上，使用的关键字为full outer join或者full join。

select

from 表1

full join 表2

on 匹配条件

自连接

还有一种特殊情况即自连接，在Oracle中一个表无法与自己进行比较，所以当需要自己表的两个信息做比较的时候也需要使用连接来连接，即同一张表的连结查询。

（3）子查询

子查询是指嵌套在其他sql语句中的select语句，也叫嵌套查询。sql语句执行顺序为从右到左执行，所以在执行查询时会先执行左侧的子查询后进行主查询。

子查询分为单行子查询和多行子查询，单行子查询是指返回一行数据的子查询语句，多行子查询是指返回多行数据的查询语句。子查询还可以分为多列子查询、多行子查询、多列多行子查询。

在进行子查询时如果内部查询不返回任何记录，则外部条件中字段DEPTNO与NULL比较永远为假，也就是说外部查询不返回任何结果。

总结为：

单行子查询是指子查询只返回单列、单行数据

多行子查询是指返回单列多行数据，都是针对单列而言的

多列子查询则是指查询返回多个列数据的子查询语句

单行子查询

where deptno = (单行数值)

多行子查询

where deptno in ( 多行数值 )

多列子查询：

where (job,deptno)=(select job,deptno from emp where ename='KING')

多列多行子查询

where (job,deptno) in (select job,deptno from emp where ename='KING')

单行子查询

在单行子查询的外部查询中可以使用=、>、<、>=、<=、<>等比较运算符。

内部查询返回的结果必须与外部查询条件中字段(DEPTNO)相匹配。

多行子查询

在WHERE子句中使用多行子查询时，可以使用多行比较运算符（IN,ALL,ANY）。

IN：等于任何一个。

ALL：和子查询返回的所有值比较。例如：sal>ALL(1,2,3)等价于sal>3，即大于所有。

ANY：和子查询返回的任意一个值比较。例如：sal>ANY(1,2,3)等价于sal>1，即大于任意一个就可以。

注：ANY运算符必须与单行比较运算符结合使用,并且返回行只要匹配子查询的任何一个结果即可。

多列子查询

多列子查询和多行子查询相同，只是使用多列子查询的时候会有多列进行匹配。

（4）集合运算

为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus。

union：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行

union all：该操作与union相似，但是它不会取消重复行，而且不会排序

intersect：使用该操作符用于取得两个结果集的交集

minus：使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据

总结为集合运算就是将两个或者多个结果集组合成一个结果集。

intersect 交集返回两个查询共有的记录

union all 并集返回各个查询的所有记录，包括重复的记录

union 交集返回各个查询的所有记录，不包括重复的记录

MINUS 补集返回第一个查询检查出的记录减去第二个查询检索出来的记录之后剩余的记录

注意：当使用集合操作的时候，查询所返回的列数以及列的类型必须匹配，列名可以不同。

（1）Distinct关键字

在Oracle中，可能出现若干相同的情况，那么可以用Distinct消除重复行

（2）多表查询与单行子查询可以实现相同的功能

查询出销售部（sales）下面的员工姓名，工作，工资

（3）显示高于自己部门平均工资的员工信息

分析：

1.找到所有部门的平均工资

select deptno,avg(sal) from emp group by deptno;

2.找到所有人的工资信息

select ename,sal,deptno from emp;

3.把两个结果集使用多表连接组合组合起来

select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) damao where emp.deptno=damao.deptno;

4.去掉低于平均工资的那些数据即可：

select * from emp,(select deptno,avg(sal) avgsal from emp group by deptno) damao where emp.deptno=damao.deptno and sal>avgsal;

（4）emp表介绍

字段类型描述

empno NUMBER(4) 表示雇员编号，是唯一编号

ename VAECHAR2(10) 表示雇员姓名

job VARCHAR2(9) 表示工作职位

mgr NUMBER(4) 表示一个雇员的领导编号

hiredate DATE 表示雇佣日期

sal NUMBER(7,2) 表示月薪，工资

comm NUMBER(7,2) 表示奖金，或者称为佣金

deptno NUMBER(2) 部门编号

Oracle之查询详解

oracle两张表关联查询

select e.empno, e.ename, d.deptno, d.dname

from emp e, dept d

where e.deptno = d.deptno;

在之前所使用的查询操作之中，都是从一张表之中查询出所需要的内容，那么如果现在一个查询语句需要显示多张表的数据，则就必须应用到多表查询的操作，而多表查询的语法如下：

SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名] ,…] FROM 表名称 [别名], [表名称 [别名] ,…] [WHERE 条件(S)] [ORDER BY 排序字段 [ASC|DESC] [,排序字段 [ASC|DESC] ,…]]。

扩展资料：

Oracle 常用的关联查询：

Oracle外连接：

（1）左外连接 (左边的表不加)。

（2）右外连接(右边的表不加)。

（3）全外连接(左右两表都不加)。

outer join则会返回每个满足第一个（顶端）输入与第二个（底端）输入的联接的行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。

外连接分为三种：左外连接，右外连接，全外连接。对应SQL：LEFT/RIGHT/FULL OUTER JOIN。通常我们省略outer 这个关键字。写成：LEFT/RIGHT/FULL JOIN。

在左外连接和右外连接时都会以一张表为基表，该表的内容会全部显示，然后加上两张表匹配的内容。如果基表的数据在另一张表没有记录。那么在相关联的结果集行中列显示为空值（NULL）。

oracle数据库三张表字段一模一样,怎么多表查询啊

通过union方式进行多表查询.

例如：

SELECT 字段1，字段2，字段3 from 表1

union

SELECT 字段1，字段2，字段3 from 表2

union

SELECT 字段1，字段2，字段3 from 表2

补充：

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。