Oracle DB uses subqueries to resolve queries

? Define subqueries? Describe the types of problems that subqueries can solve? List the types of subqueries? Write single-line and multiline subqueries? Subquery: Type, syntax, and guidelines? Single-row subquery: – Group function in a subquery – having clauses with subqueries? Multiline subquery – Use the all or any operator? Using the EXISTS operator? Null values in a subquery

• Using subqueries to resolve issues

Whose salary is higher than Abel's salary? Subquery: What is the salary of Abel? Main query: Which employees pay more than Abel's salary? Using subqueries to solve problems suppose you want to write a query to find out whose salary is higher than Abel's salary. To resolve this issue, you need to use two queries: One query is used to find Abel salaries, and the other is used to find people who pay more than that amount. This problem can be resolved by combining these two queries and placing one query in another query. An internal query (that is, a 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.

• Child query syntax

? Execute the subquery (internal query) before executing the main query (external query). The primary query uses the results of the subquery.

SELECT  from Table WHERE expr operator (SELECTfromtable);

A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. By using subqueries, you can build powerful statements with simple statements. Subqueries are useful when you need to select rows from a table and the selection criteria depend on the data in the table itself. You can use subqueries in many SQL clauses, including the following clauses:? WHERE clause? Having clauses? The FROM clause is in this syntax: operator includes comparison criteria, such as >, =, or in note: The comparison condition is divided into the following two classes: Single-line operators (>, =, >=, <, <>, <=) and multiline operators (in, any, all, EXISTS). Subqueries are often referred to as nested SELECT statements, sub-SELECT statements, or internal SELECT statements. A subquery is typically executed first, and then its output is used to refine the query criteria for the main query (that is, the external query).

• Working with sub-queries

SELECT  from Employees WHERE > (SELECTfromWHERE='Abel');

In the example, the internal query determines the salary of the employee Abel. An external query takes the results of an internal query and shows that the salary exceeds the employee's Abel for all employees based on this result.

• Guidelines for using sub-queries

? The subquery must be enclosed in parentheses. Subqueries are placed on the right side of the comparison condition to increase readability. However, a subquery can appear on either side of the comparison operator. There are two kinds of comparison criteria that can be used in a subquery: a single-line operator and a multi-line operation. Use the single-line operator for a single-row subquery and use the multiline operator for multiline subqueries.

• The type of child query

? Single-line subquery: A query that returns only one row from an internal SELECT statement? Multiline subquery: Query that returns multiple rows from an internal SELECT statement Note: In addition, there are multiple-column subqueries, which return multiple columns from an internal SELECT statement.

• Single-line subquery

Returns only one row using a single-line comparison operator-line subquery is a query that returns a row from an internal SELECT statement. This type of subquery uses a single-line operator. Gives a list of single-line operators. Example: Show employees whose job ID is the same as employee 141 's job ID:

SELECT  from Employees WHERE = (SELECTfromWHERE=141);  

• Execute a single-line subquery

SELECTlast_name,job_id, Salary fromEmployeesWHEREjob_id= (SELECTjob_id fromEmployeesWHERELast_Name= 'Taylor') andSalary>(SELECTSalary fromEmployeesWHERELast_Name= 'Taylor'); 

You can treat a SELECT statement as a query block. The example shows an employee whose job title is the same as Taylor but whose salary is higher than Taylor. The example consists of three query blocks: an external query and two internal queries. The internal query blocks are executed first, and the resulting query results are sa_rep and 8600, respectively. You can then process the outer query block, using the values returned by the internal query to refine its search criteria. Two internal queries return a single value (Sa_rep and 8600, respectively), so this SQL statement is called a single-row subquery. Note: external queries and internal queries can get data from different tables.

• Using group functions in subqueries

SELECT  from Employees WHERE = (SELECTMIN from

You can display data from the main query by using group functions in a subquery to return a single line. The subquery is contained in parentheses after the comparison condition. The example shows the last name, job ID, and salary of all employees whose salaries are equal to the minimum salary. The Min group function returns a single value (2500) to an external query.

• HAVING clause with sub-query

? Oracle Server executes subqueries first. Oracle Server Returns the results to the HAVING clause of the main query.

SELECT MIN  from GROUP  by department_id  having MIN > (SELECTMINfromWHERE=

You can use subqueries not only in the WHERE clause, but also in the HAVING clause. The Oracle server executes the subquery and returns the result to the HAVING clause of the main query. The SQL statement in the example shows all departments with a minimum salary higher than the minimum salary for department 50. Example: Find a job with the lowest average salary.

SELECT AVG  from GROUP  by job_id  having AVG = (SELECTMIN(AVGfromGROUP by job_id);

• What is wrong in this statement

A single-line operator is used for multiline subqueries

connected.hr@TEST0924> SELECTemployee_id, last_name fromEmployees2  WHERESalary=  3(SELECT MIN(Salary) fromEmployeesGROUP  bydepartment_id);(SELECT MIN(Salary) fromEmployeesGROUP  bydepartment_id)*ERROR at line3: ORA-01427: Single-Row subqueryreturnsMore than one row

A common error with subqueries is that a single-line subquery returns more than one row. In the example SQL statement, the subquery contains a GROUP BY clause, which means that the subquery returns more than one row, each corresponding to a group that it finds. In this case, the result of the subquery is 4400, 6000, 2500, 4200, 7000, 17000, and 8300. The external query takes these results and uses them in its where clause. The WHERE clause contains a equals (=) operator, which is a single-line comparison operator that requires only one value. The = operator could not accept multiple values in the subquery, resulting in an error. To correct this error, change the = operator to in.

• Internal query does not return any rows

Because there are no employees named "Haas," the subquery does not return any rows.

HR@TEST0924>SELECT from employees  2  WHERE  =  3  (SELECTfromWHERE='  Haas'

No rows selectedAnother common problem with subqueries is that internal queries do not return any rows. In the example's SQL statement, the subquery contains a WHERE clause. Presumably, the purpose of the statement was to look for an employee named Haas. The statement is correct, but because there are no employees named "Haas", no rows are selected for execution. Therefore, the subquery does not return any rows. An external query takes the result of a subquery (a null value) and uses those results in its where clause. An external query did not find an employee with a job ID equal to a null value, so no rows were returned. Even if a job with a null value exists, the row is not returned because a comparison of two null values produces a null value so that the where condition is not true.

• Multi-row subqueries

? Return multiple rows? Subqueries that use multiline comparison operators to return multiple rows are called multi-row subqueries. You should use multiline operators instead of single-line operators for multiline subqueries. Multiple-line operators require one or more values:

SELECT  from Employees WHERE inch (SELECTMINfromGROUP by department_id);

Example: Find an employee whose salary equals the minimum salary for each department. The internal query is executed first, and a query result is generated. You can then process the main query block, using the values returned by the internal query to refine its search criteria. In fact, the main query will appear in the following form on Oracle Server:

SELECT  from Employees WHERE inch (250042004400600070008300860017000 );

• Using the any operator in multiline subqueries

SELECT  from Employees WHERE <  any (SELECTfromWHERE='it_prog' )and<>'it_prog';

The any operator, and its synonym some operator, is used to compare a value to each value returned by the subquery. The example shows an employee who is not an IT programmer and has a lower salary than any it programmer. The maximum salary for programmers is $9,000. <any indicates a lower than maximum value.? >any represents above the lowest value.? =any equals in. Reference: http://blog.csdn.net/rlhua/article/details/12007309

• Using the all operator in multiline subqueries

SELECT employee_id, last_name, job_id, salary from Employees WHERE Salary < all (SELECT salary from Employees WHERE job_id = ' It_prog ') and job_id <> ' It_prog ';Use the all operator all operator in a multiline subquery to compare a value to each value returned by the subquery. The example shows the salary of any employee who has a salary lower than the job ID of It_prog and is not a It_prog employee. >all represents greater than the highest value, while <all represents less than the lowest value. The NOT operator can be used with the in, any, and all operators. Reference: http://blog.csdn.net/rlhua/article/details/12006433 using the EXISTS operator

SELECT *  from WHERE  not EXISTS (SELECT*fromWHERE employees.department_id= DEPARTMENTS.DEPARTMENT_ID);

Use the EXISTS operator with the EXISTS operator in a query, and the results depend on whether some rows exist in the table. Evaluates to TRUE if the subquery returns at least one row. The example shows a department with no employees. For each row in the Departments table, check the conditions to see if a row with the same department ID exists in the Employees table. If no such row exists, the corresponding row satisfies the condition, and the row is selected. If the corresponding row exists in the Employees table, the row is not selected.

• Null values in a subquery

SELECT  from WHERE  not inch (SELECT from Employees MGR);

The SQL statement in the example attempts to display all employees without any subordinates. Logically, this SQL statement should return 12 rows. However, the SQL statement does not return any rows. Because one of the values returned by an internal query is a null value, the entire query does not return any rows. The reason is that when all conditions are compared to null values, a null value is generated. Therefore, do not use the not in operator as long as the result set of the subquery may contain null values. The not in operator equals <> all. Note that if you are using the in operator, a null value in the result set of the subquery will not be an issue. The in operator is equivalent to =any. For example, to display an employee who has subordinates, you can use the following SQL statement:

SELECT  from WHERE inch (SELECT from Employees MGR);

In addition, you can include a WHERE clause in a subquery to display all employees without subordinates:

 from WHERE  not inch (SELECTfromWHEREare notNULL);

• Summary

A subquery is a SELECT statement that is embedded in the clause of another SQL statement. Using subqueries is helpful when the query is based on search criteria with unknown intermediate values. The subquery has the following characteristics:? Can you pass a row of data to a main statement that contains a single-line operator (such as =, <>, >, >=, <, or <=)? Can you pass multiple rows of data to a main statement that contains a multiline operator, such as in? Oracle Server processes the subquery first, and then the WHERE or HAVING clause uses the resulting result? Can contain group functions

Oracle DB uses subqueries to resolve queries