SQL Sub-query

Sub-query refers to a complete query, nested in a number of different functions of small queries, so as to complete a complex query a form of writing, in order to let the reader more clearly understand the concept of sub-query.Sub-query returns resultsThere are four types of data that can be returned by subqueries:

1. Single row: Returns the contents of a specific column, which can be understood as a single-valued data;
2. Single-row multi-column: Returns the contents of multiple columns in a row of data;
3. Multi-row column: Returns the contents of the same column in a multiline record, which is equivalent to giving a range of operations;
4. Multiline multiple columns: The result returned by the query is a temporary table;

using subqueries in the WHERE clausein the WHERE clause, a single-line subquery, a multiline single-row subquery, and a single-line multi-column subquery are processed. Single-row sub-query Example One,Find complete information about the lowest paid employees in the company--Find out the full information of the lowest paid employees in the company SELECT * fromEMP EWHEREE.sal=(SELECT MIN(SAL) fromEMP);example Two,Find out all employee information with lower base pay than Allen--Find out all employee information for lower base pay than Allen SELECT * fromEMP EWHEREE.sal<(SELECTSal fromEmpWHEREEname=' ALLEN ');example Three,Query all employee information for base pay higher than the company's average salary--Query all employee information for base salary above company average SELECT * fromEMP EWHEREE.sal>(SELECT AVG(SAL) fromEMP);single-row multi-column subquery. example Four,Find out the same job as Allen, and the base salary is higher than all employee information for employee number 7521,--Find out the same job as Allen, and the base salary is higher than all employee information for employee number 7521, SELECT * fromEMP EWHEREE.job=(SELECTJob fromEmpWHEREEname=' ALLEN ') andE.sal>(SELECTSal fromEmpWHEREEmpno=7521);example Five,Inquire about employees with the same salary as Scott doing the same jobSELECT * fromEMP EWHERE(e.job,e.sal)=(SELECTJob,sal fromEmpWHEREEname=' SCOTT ') andEname<>' SCOTT ';example Six,Query all employee information that is in the same job with employee 7566 and leads the same--Query all employee information that is in the same job with employee 7566 and leads the same SELECT * fromEMP EWHERE(e.job,e.mgr)=(SELECTJob,mgr fromEmpWHEREEmpno=7566);example Seven,Query all employee information (including Allen) in the same job as Allen and employed in the same year--Query all employee information (including Allen) in the same job as Allen and employed in the same year SELECT * fromEMP EWHERE(E.job,to_char (E.hiredate,' yyyy '))=(SELECTJob,to_char (HireDate,' YYYY ') fromEmpWHEREEname=' ALLEN ');Multiline single-row subqueryThe main use of three operators: in, any, allIn operationexample Eight,Query all employee information that is the same as the minimum wage in each department--Check out all employee information that is the same as the minimum wage in each department SELECT * fromEMP EWHEREE.salinch(SELECT MIN(SAL) fromEmpGROUP byDEPTNO);Example Nine,Find out all employee information that is not the same as the minimum wage in each department--Check out all employee information that is not the same as the minimum wage in each department SELECT * fromEMP EWHEREE.sal not inch(SELECT MIN(SAL) fromEmpGROUP byDEPTNO);any is used in the following three forms of use:=any: Represents a comparison with each element in a subquery, functionally similar to in (however <>any is not equivalent to not in) >any: larger than the smallest return result in a subquery (also includes >=any) < Any: smaller than the largest returned result in a subquery (also includes <=any)Example Ten,Find out the salary of each department manager--Find out the salary of each department manager SELECT * fromEmpWHERESal= any(SELECT MIN(SAL) fromEmpWHEREJob=' MANAGER ' GROUP byDEPTNO);Example Xi.,Find out the salary of each department greater than the manager--Find out the salary of each department greater than the manager SELECT * fromEmpWHERESal> any(SELECT MIN(SAL) fromEmpWHEREJob=' MANAGER ' GROUP byDEPTNO);Example 12,Find out the salary of each department less than the manager--Check out the salary of each department less than the manager SELECT * fromEmpWHERESal< any(SELECT MIN(SAL) fromEmpWHEREJob=' MANAGER ' GROUP byDEPTNO);The all operator has the following three ways to use:<>all: Equivalent to not in (but =all is not equivalent to in) >all: larger than the largest value in the subquery (also includes >=all) <all: Smaller than the smallest value in the subquery (also includes <=all)Example 13,Find out that each department is not equal to the manager's salary- -Find out that each department is not equal to the manager's salary SELECT * fromEmpWHERESal<> All(SELECT MIN(SAL) fromEmpWHEREJob=' MANAGER ' GROUP byDEPTNO);Example 14,SELECT * fromEmpWHERESal< All(SELECT MIN(SAL) fromEmpWHEREJob=' MANAGER ' GROUP byDEPTNO);Example XV,SELECT * fromEmpWHERESal>all(SELECT MIN(SAL) fromEmpWHEREJob=' MANAGER ' GROUP byDEPTNO); Null data judgment provides a exists structure in SQL to determine if a subquery has data to return. If there is data returned in the subquery, the EXISTS structure returns true, whereas the reverse returns false.Example XV,Verifying the EXISTS structure--Verify the EXISTS structure SELECT * fromEmpWHERE EXISTS(--return null value, no content output SELECT * fromEmpWHEREEmpno=9999);--Employees without this numberExample 16, SELECT * fromEmpWHERE EXISTS(SELECT * fromEMP);--Content will return dataExample 17, SELECT * fromEmpWHERE not EXISTS(SELECT * fromEMP);--there is data, but return, no content output

SQL Sub-query