? 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?Sub-query:What is the salary of Abel? MainEnquiry: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 work around this problem, you need two queries: one query to find the salary for Abel, and another to finda person with a salary exceeding 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 subqueries is equivalent toexecutes two successive queries, and uses the result of the first query as the search value in the second query.
? Execute the subquery (internal query) before executing the main query (externalquery). ? The primary query uses the results of the subquery.
SELECT select_list from table WHERE expr operator
(SELECT select_list from table); A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. By using subqueries,powerful statements can be built with simple statements. When you need to select rows from a table, the selection criteria depend on thesubqueries are useful when you have 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 multiple-line operators (in,Any , all, EXISTS). Subqueries are often referred to as nested SELECT statements, sub-SELECT statements, or internal SELECT statements. Usually performed firstSubquery , and then uses its output to refine the query criteria for the main query (that is, the external query).
SELECT last_name, salary from employees
WHERE Salary >
(SELECT salary from employees WHERE last_name = ' Abel '); in the example, the internal query determines the salary of the employee Abel. External queries take the results of internal queries and are based onThis result shows that the salary exceeds the employee Abel all employees.
- 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 in any of the comparison operatorsside. ? 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, using themulti-line operator.
? 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.
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 last_name, job_id from employees
WHERE job_id =
(SELECT job_id from employees WHERE employee_id = 141);
- Execute a single-line subquery
SELECT last_name,job_id, salary from employees
WHERE job_id =
(SELECT job_id from employees WHERE last_name= ' Taylor ')
> Salary
(SELECT salary from employees WHERE last_name= ' Taylor '); You can treat a SELECT statement as a query block. Example shows that their job title is the same as Taylor but the salaryemployees above Taylor's. The example consists of three query blocks: an external query and two internal queries. The inner query block is executed first, the generatedthe query results were sa_rep and 8600, respectively. You can then process the outer query block, using the values returned by the internal query torefine its search criteria. Each of the two internal queries returns a single value (Sa_rep and 8600, respectively), so this SQL statement is called a single rowSub-query. Note: external queries and internal queries can get data from different tables.
- Using group functions in subqueries
SELECT last_name,job_id, salary from employees
WHERE salary =
(SELECT MIN (salary) from employees); You can display data from the main query by using group functions in a subquery to return a single line. The subquery is enclosed in parentheses,is 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. Min Group Letterthe number returns a single value (2500) to the 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 department_id, MIN (salary) from employees GROUP by department_id
Having MIN (salary) >
(SELECT MIN (salary) from employees WHERE department_id =); You can use subqueries not only in the WHERE clause, but also in the HAVING clause. OracleThe 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 job_id, AVG (Salary)From employeesGROUP by job_idHaving AVG (Salary) = (SELECT MIN (avg (Salary))From employeesGROUP by job_id);
- What is wrong in this statement
Querying on multiple rows of subqueriesa single-line operator is usedConnected.
[email protected]> SELECT employee_id, last_name from Employees
2 WHERE salary =
3 (SELECT MIN (salary) from Employees GROUP by department_id);
(SELECT MIN (salary) from Employees GROUP by department_id)
*
ERROR at line 3:
Ora-01427:single-row subquery returns more than one rowA 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 returnsMultiline , each line corresponds to a group 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 aequals (=) operator, which is a single-line comparison operator that requires only one value. = operator cannot accept childmultiple values in the query, 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.
[email protected]> SELECT last_name, job_id from Employees
2 WHERE job_id =
3 (SELECT job_id from employees WHERE last_name = ' 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. It is speculated that the purpose of the statement is to findan employee named Haas. The statement is correct, but because there are no employees named "Haas", there is no option to executeSelect any line. 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. External query notan employee with a job ID equal to a null value is found, so no rows are returned. Even if a job with a null value exists, it will notreturns a row, because a comparison of two null values produces a null value, so that the where condition is not true.
? Return multiple rows? Subqueries that use multiline comparison operators to return multiple rows are called multi-row subqueries. Multi-row subqueries should be used with multiline operators instead of using a single lineoperator. Multiple-line operators require one or more values:
SELECT last_name, salary, department_id from employees
WHERE Salary in
(SELECT MIN (salary) from employees GROUP 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 queryrefine its search criteria. In fact, the main query will appear in the following form on Oracle Server:
SELECT last_name, salary, department_id from employees
WHERE Salary in (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);
- Using the any operator in multiline subqueries
SELECT employee_id, last_name, job_id, salary from employees
WHERE Salary < any
(SELECT salary from employees WHERE job_id = ' It_prog ')
and job_id <> ' 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. Top salary for programmersfor $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. Example shows salary belowThe salary of any employee who has a 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 Departments WHERE not EXISTS
(SELECT * FROM Employees WHERE 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. If the subquery is at leastreturns a row, evaluates to True. The example shows a department with no employees. For each row in the Departments table, check the conditions to seeSee if a row with the same department ID exists in the Employees table. If no such row exists, the correspondingThe row satisfies the condition so that the row is selected. If the corresponding row exists in the Employees table, the row is not selected.
- Null values in a subquery
SELECT emp.last_name from employees EMP WHERE emp.employee_id not in
(SELECT mgr.manager_id from employees MGR); The SQL statement in the example attempts to display all employees without any subordinates. Logically, this SQL statement should return to theback to Line 12. However, the SQL statement does not return any rows. Because one of the values returned by the internal query is a null value, Therefore, the entire query does not return any rows . cause a null value is generated when all conditions are compared to null values。 Soas long as the result set of the subquery may contains null values, do not use the not in operator . 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. Inchthe operator is equivalent to =any. For example, to display an employee who has subordinates, you can use the following SQL statement:
SELECT emp.last_name from employees EMP WHERE emp.employee_id in
(SELECT mgr.manager_id from employees MGR); In addition, you can include a WHERE clause in a subquery to show all employees without subordinates: SELECT last_name from EmployeesWHERE employee_id not in(SELECT manager_idFrom employeesWHERE manager_id is not NULL);
A subquery is a SELECT statement that is embedded in thethe clause in another SQL statement. When a query is based on a search criterion with an unknown intermediate value, using a subquery is verybe helpful. 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 function source: http://blog.csdn.net/rlhua/article/details/12879585
