oracle 子查询
子查询就是嵌套在查询语句内的查询，后续查询可以使用先前查询的结果，用以代替暂存表或变量存储先前查询结果。 子查询的外层为较高的层级，以下称之为“主查询”。派生表是子查询的一个特殊情况，他是应用于FROM子句中的 SELECT完整命令，以数据表别名引用
子查询就是嵌套在查询语句内的查询，后续查询可以使用先前查询的结果，用以代替暂存表或变量存储先前查询结果。
子查询的外层为较高的层级，以下称之为“主查询”。派生表是子查询的一个特殊情况，他是应用于FROM子句中的
SELECT完整命令，以数据表别名引用此子查询时，同使用一般数据表一样。
子查询以内外层是否进行连接（JOIN）可分成嵌套子查询和相关子查询两种类型
一、嵌套子查询
1、单行子查询
单行子查询将传回单一字段（Column）的一行（Row）数据值，可应用于任何需要纯量值的场合，从而简化查询中需要使用
变量的那些需求。以下范例将查询学校为广州大学所在的单位的所有人员，其中子查询用来查询学校为广州大学的部门ID
主查询则利用子查询所传回的部门编号查询人员数据表
select name,account,unit_id from sys_user where unit_id = ( select id from sys_unit where name = '广州大学' )
2、多行子查询
多行子查询将传回单一字段（Column）的多行（Rows）数据值，当子查询传回多笔数据值时，必须改用IN。将上述范例增加查询
另一个学校名称为广东工业大学的部门编号
select name,account,unit_id
from sys_user
where unit_id in
(
select id from sys_unit
where name in('广州大学','广东工业大学')
)
二、相关子查询
下面介绍将EXISTS应用于相关子查询的方法。EXISTS是用来检查某个数据是否符合子查询的条件，相反为NOT EXISTS.。
当在子查询中找到符合条件的数据时，EXISTS将传回TRUE，而这笔数据则被外层查询传回。
select name
from sys_user u
where exists
(
select 1 --可为任何常数，如数值1
from sys_unit unit
where u.unit_id = unit.id and name in('广州大学','广东工业大学')
)
嵌套子查询与相关子查询的差别在于：子查询是否可以单独单独执行，相关子查询必须与外层查询相互关联，外层查询必须将每一笔
数据传入子查询进行比对，符合子查询的数据最后才会被外层查询传回。
1、派生表
派生表是应用于FORM子句中的子查询，将子查询中的数据暂时视为一个虚拟数据表以供主查询使用。子查询时一完整查询，
可使用数据表连接、GROUP BY甚至是ORDER BY子句，将子查询置于主查询的FORM子句后，需将子查询置于括号内并予以命名，像使用一般数据表那样。
select u.name
from sys_user u,
(
select id,name
from sys_unit where name in('广州大学','广东工业大学')
) t
where u.unit_id = t.id
2、通用表表达式
对于较复杂的查询命令，有时将需要重复运用相同的子查询，采用派生表的作法将耗用数据库许多资源，造成执行效率低下等问题。为避免重复执行相同的子查询，建议采用通用表表达式，即WITH命令。
with Emp
as
(
select id,name
from sys_unit where name in('广州大学','广东工业大学')
)
--后续操作与一般表的使用方式向他
select u.name
from sys_user u,Emp e
where u.unit_id = e.id;
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
What to do if the oracle can't be opened
Apr 11, 2025 pm 10:06 PM
Solutions to Oracle cannot be opened include: 1. Start the database service; 2. Start the listener; 3. Check port conflicts; 4. Set environment variables correctly; 5. Make sure the firewall or antivirus software does not block the connection; 6. Check whether the server is closed; 7. Use RMAN to recover corrupt files; 8. Check whether the TNS service name is correct; 9. Check network connection; 10. Reinstall Oracle software.
How to solve the problem of closing oracle cursor
Apr 11, 2025 pm 10:18 PM
The method to solve the Oracle cursor closure problem includes: explicitly closing the cursor using the CLOSE statement. Declare the cursor in the FOR UPDATE clause so that it automatically closes after the scope is ended. Declare the cursor in the USING clause so that it automatically closes when the associated PL/SQL variable is closed. Use exception handling to ensure that the cursor is closed in any exception situation. Use the connection pool to automatically close the cursor. Disable automatic submission and delay cursor closing.
How to create cursors in oracle loop
Apr 12, 2025 am 06:18 AM
In Oracle, the FOR LOOP loop can create cursors dynamically. The steps are: 1. Define the cursor type; 2. Create the loop; 3. Create the cursor dynamically; 4. Execute the cursor; 5. Close the cursor. Example: A cursor can be created cycle-by-circuit to display the names and salaries of the top 10 employees.
How to paginate oracle database
Apr 11, 2025 pm 08:42 PM
Oracle database paging uses ROWNUM pseudo-columns or FETCH statements to implement: ROWNUM pseudo-columns are used to filter results by row numbers and are suitable for complex queries. The FETCH statement is used to get the specified number of first rows and is suitable for simple queries.
How to stop oracle database
Apr 12, 2025 am 06:12 AM
To stop an Oracle database, perform the following steps: 1. Connect to the database; 2. Shutdown immediately; 3. Shutdown abort completely.
What steps are required to configure CentOS in HDFS
Apr 14, 2025 pm 06:42 PM
Building a Hadoop Distributed File System (HDFS) on a CentOS system requires multiple steps. This article provides a brief configuration guide. 1. Prepare to install JDK in the early stage: Install JavaDevelopmentKit (JDK) on all nodes, and the version must be compatible with Hadoop. The installation package can be downloaded from the Oracle official website. Environment variable configuration: Edit /etc/profile file, set Java and Hadoop environment variables, so that the system can find the installation path of JDK and Hadoop. 2. Security configuration: SSH password-free login to generate SSH key: Use the ssh-keygen command on each node
How to create oracle dynamic sql
Apr 12, 2025 am 06:06 AM
SQL statements can be created and executed based on runtime input by using Oracle's dynamic SQL. The steps include: preparing an empty string variable to store dynamically generated SQL statements. Use the EXECUTE IMMEDIATE or PREPARE statement to compile and execute dynamic SQL statements. Use bind variable to pass user input or other dynamic values to dynamic SQL. Use EXECUTE IMMEDIATE or EXECUTE to execute dynamic SQL statements.
What to do if the oracle log is full
Apr 12, 2025 am 06:09 AM
When Oracle log files are full, the following solutions can be adopted: 1) Clean old log files; 2) Increase the log file size; 3) Increase the log file group; 4) Set up automatic log management; 5) Reinitialize the database. Before implementing any solution, it is recommended to back up the database to prevent data loss.
