Subquery One (sub-query in where)

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 results
There are four types of data that can be returned by subqueries:

1. 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 clause in 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, the full information of the employee with the lowest wage in the company is queried

-- find complete information about the lowest paid employees in the company SELECT *  from EMP E WHERE e.sal=(  SELECTMIN(sal)     from EMP);

example Two, query out all employee information with lower base pay than Allen

-- Find out all employee information with lower base pay than Allen SELECT *  from EMP E WHERE e.sal<SELECT  sal  from  emp   WHERE ename='ALLEN');

Example Iii. query all employee information for a higher base pay than the company's average salary

-- query all employee information for base pay higher than the company's average salary SELECT *  from EMP E WHERE e.sal>(SELECTAVG(sal) from EMP);

single-row multi-column subquery.

example Four, find out the same work with Allen, and the base salary is higher than the employee number 7521 of all employee information,

--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 v. query employee information with the same salary as Scott in the same job

 select  *    emp e  where  (e.job,e.sal) Span style= "color: #808080;" >=   ( select   job,sal  from   emp  where  ename=   " scott   " )   ename<>   " scott  ; 

example Six, querying 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 *  from EMP E WHERE = (        SELECT  job,mgr        from  emp      WHERE Empno=7566);

Example VII query all employee information (including Allen) that is employed by Allen in the same job and in the same year

--Query all employee information (including Allen) in the same job as Allen and employed in the same yearSELECT * fromEMP EWHERE(E.job,to_char (E.hiredate,'yyyy'))=(           SELECTJob,to_char (HireDate,'YYYY')             fromEMPWHEREEname='ALLEN');

Multiline single-row subquery

The main use of three operators: in, any, all In operation

example Eight, querying all employee information that is the same as the minimum wage in each department

-- query All employee information that is the same as the minimum wage in each department SELECT *  from EMP E WHERE inch (     SELECTMIN(sal)    from  emp      GROUP by Deptno);

example Nine, querying all employee information that is not the same as the minimum wage in each department

 --  query out all employee information that is not the same as the minimum wage in each department  select  *  from   EMP e  where  e.sal not  in   ( select  min   from   EMP  group  by   Deptno);  

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 return result in a subquery (also includes <=any)

Example Ten, find out the salary of each department manager

 --  Query the payroll for each department manager  select  *  from   EMP  where  sal =  any   ( select  min   from   EMP  where  job=   '  manager   '  

Example Xi., query for wages that are greater than the manager for each department

 --  Query the salary of each department greater than the manager  select  *  from   EMP  where  sal >  any   ( select  min   from   EMP  where  job=   '  manager   '  

Example 12, querying wages per department less than manager

 --  Query the payroll for each department less than the manager  select  *  from   EMP  where  sal <  any   ( select  min   from   EMP  where  job=   '  manager   '  

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 a subquery (also includes >=all)
<all: Smaller than the smallest value in the subquery (also includes <=all)

Example 13, query out each department is not equal to the manager's salary

 --  Find out that each department is not equal to the manager's salary  select  *  from   EMP  where  sal <>  all   ( select  min   from   EMP  where  job=   '  manager   '  

Example 14,

SELECT  *  from  WHERE< all (   SELECTMIN  (SAL)    from EMP   WHERE Job='MANAGER'  GROUP by deptno);

Example XV,

SELECT  *  from  WHERE>all  (   SELECTMIN  (sal)    from emp  WHERE Job='MANAGER'  GROUP  by deptno);

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

-- Verifying the EXISTS structure SELECT *  from EMP     WHERE EXISTS (   - - return null value, no content output      SELECT*fromWHERE Empno=9999- employees without this number

Example 16,

SELECT *  from  WHEREEXISTS(SELECT* from EMP); -- There is content that will return the data

Example 17,

  SELECT *  from  WHEREnotEXISTS(SELECT* from EMP); -- there is data, but cashback, no content output

Subquery One (sub-query in where)