Oracle DB uses subqueries to solve queries

Define a subquery

• Describes the types of problems that subqueries can solve • lists the types of subqueries • Write single-row and multi-row subqueries • subqueries: types, syntaxes, and guidelines • single-row subqueries: -Group function in subquery-HAVING clause with subquery • multi-row subquery-use ALL or ANY operator • Use EXISTS operator • null value in subquery

• Use subquery to solve the problem

Whose salary is higher than Abel's? Subquery: What is Abel's salary? Primary query: which employees have higher salaries than Abel's? Use a subquery to solve the problem. Suppose you need to write a query to find out who has higher salaries than Abel. To solve this problem, two queries are required: one query is used to find the Abel salary, and the other query is used to find the person whose salary exceeds this amount. By combining these two queries, placing one query in another query can solve this problem. An internal query (subquery) returns the value to be used by an external query (that is, the primary query. Using a subquery is equivalent to executing two consecutive queries, and the result of the first query is used as the search value in the second query.

• Subquery syntax

• Execute the subquery (internal query) and then the primary query (external query ). • The primary query uses the subquery results.SELECT select_list FROM table WHERE expr operator(SELECT select_list FROM table );A subquery is a SELECT statement embedded in the clause of another SELECT statement. By using subqueries, you can use simple statements to build powerful statements. When you need to select rows from the table, but the selection condition depends on the data in the table itself, subqueries are very useful. Subqueries can be used in many SQL clauses, including the following clauses: • WHERE clause • HAVING clause • FROM clause in this syntax: operator includes comparison conditions, for example,>, =, or IN Note: Comparison conditions are divided into the following two types: single-line operators (>, =, >=, <, <>, <=) and multiline operators (IN, ANY, ALL, EXISTS ). Subqueries are usually called nested SELECT statements, Subselect statements, or internal SELECT statements. Generally, a subquery is executed first, and then its output is used to improve the query conditions of the primary query (that is, external query.

• Use subquery

SELECT last_name, salary FROM employeesWHERE salary>(SELECT salary FROM employees WHERE last_name = 'abel ');In the example, the internal query determines the employee Abel's salary. The external query uses the results of the internal query and displays all employees whose salaries exceed the employee Abel.

• Criteria for using subqueries

• Subqueries must be placed in brackets. • Placing subqueries on the right of the comparison condition increases readability. However, subqueries can appear on any side of the comparison operator. • Two Types of comparison conditions can be used in a subquery: single-line and multi-line operators. A single-row subquery uses a single-row operator and a multi-row subquery uses a multi-row operator.

• Subquery type

• Single-row subquery: returns only one row of queries from an internal SELECT statement • multi-row subquery: returns multiple rows of queries from an internal SELECT statement note: In addition, there are multiple-column subqueries, this type of query returns multiple columns from an internal SELECT statement.

• Single Row subquery

Only one row of subqueries using the single row comparison operator is a query that returns one row from an internal SELECT statement. This type of subquery uses the single row operator. Returns a list of single-line operators. Example: Display employees whose job ID is the same as employee 141's job ID:SELECT last_name, job_id FROM employeesWHERE job_id =(SELECT job_id FROM employees WHERE employee_id = 141 ); 

• Execute a single row subquery

SELECT last_name, job_id, salary FROM employeesWHERE job_id =(SELECT job_id FROM employees WHERE last_name = 'taylor ')AND salary>(SELECT salary FROM employees WHERE last_name = 'taylor ');The SELECT statement can be considered as a query block. The example shows the employees whose positions are the same as Taylor but whose salaries are higher than Taylor. This example consists of three query blocks: one external query and two internal queries. First, execute the internal query block. The generated query results are SA_REP and 8600, respectively. Then, you can process external query blocks and use the values returned by the internal query to improve the search criteria. Both internal queries return a single value (SA_REP and 8600 respectively). Therefore, this SQL statement is called a single row subquery. Note: external queries and internal queries can obtain data from different tables. For more details, please continue to read the highlights on the next page:

Recommended reading:

From subquery instance application in Oracle

WITH clause: subquery name

Operations on the first and last lines of Oracle subqueries

Oracle subquery details

Basic query statement for Oracle 10 Gb database-bottom-connection & subquery

 

1 2 3 Next Page