In Oracle, the with statement can implement a subquery, which is used to create a public temporary table to improve the efficiency of statement execution. The syntax is "with tempName as (select ....)select ...".
The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.
When we write query statements in Oracle, we often write subqueries after the select clause or the from clause. This article introduces how to use the with clause. The sentence implements the subquery, and the efficiency is higher
Open the pl/sql software, log in to the oracle database using the scott user, and find the employee table (EMP)
The requirement of this example is to query the top 5 salaries For the employee information of the first name, you would generally think of sorting by salary first, and then taking out the top 5 pieces of data. Yes, you can achieve your goal.
This example uses the with statement to first define a temporary query of employee information sorted by salary, and then query the temporary query; the syntax is with queryname1 as (sql query statement) select * from queryname1.
According to relevant information, the advantage of the with clause is: if multiple identical subqueries are encountered, this method is more efficient than ordinary subqueries, and the code structure More clear and understandable.
with syntax supports allowing the definition of multiple subqueries. Multiple subqueries are separated by commas. There is no division sign between the definition and the final query statement.
Recommended tutorial: "Oracle Video Tutorial"
The above is the detailed content of What is the usage of with in oracle. For more information, please follow other related articles on the PHP Chinese website!