Subquery 1 (subquery in WHERE), where in subquery

Subquery 1 (subquery in WHERE), where in subquery
Subquery

A subquery is a compilation of complex queries by embedding several small queries with different functions in a complete query statement, to help readers better understand the concept of subqueries.

Subquery return results
Data types that can be returned by subqueries are divided into four types:

In WHERE Use subquery in Clause The WHERE clause processes single-row Single-Column subqueries, multi-row Single-Column subqueries, and single-row multi-column subqueries. Single-row Single-Column subquery  Example 1,Query the complete information of the employees with the lowest salaries in the company

-- Query the complete information of the employees with the lowest salaries in the company. SELECT * FROM emp eWHERE e. sal = (select min (sal) FROM emp );

Example 2,Query all employees whose basic salary is lower than ALLEN

-- Query all employees whose basic salary is lower than ALLEN. SELECT * FROM emp eWHERE e. sal <(SELECT sal FROM emp WHERE ename = 'allen ');

Example 3,Query Information about all employees whose basic salary is higher than the company's average salary

 

-- Query Information about all employees whose basic salary is higher than the company's average salary SELECT * FROM emp eWHERE e. sal> (select avg (sal) FROM emp );

Single Row multi-column subquery.

Example 4,Find all information about employees who are engaged in the same job as ALLEN and whose basic salary is higher than employee ID 7521,

-- Find all the information about employees who are engaged in the same job as ALLEN and whose basic salary is higher than employee ID 7521, SELECT * FROM emp eWHERE e. job = (SELECT job FROM emp WHERE ename = 'allen ') AND e. sal> (SELECT sal FROM emp WHERE empno = 7521 );

Example 5,Query Information about employees who are engaged in the same job as SCOTT and have the same salary

SELECT *FROM emp eWHERE (e.job,e.sal) = (  SELECT job,sal  FROM emp   WHERE ename='SCOTT')  AND ename<>'SCOTT';

Example 6,Query all employee information that is the same as employee 7566 and leads the same job

-- Query SELECT * FROM emp eWHERE (e. job, e. mgr) = (SELECT job, mgr FROM emp WHERE empno = 7566 );

Example 7,Query Information about all employees (including ALLEN) who have been engaged in the same job with ALLEN and have been hired for the same year)

-- Query all employee information (including ALLEN) SELECT * FROM emp eWHERE (e. job, to_char (e. hiredate, 'yyyy') = (SELECT job, to_char (hiredate, 'yyyy') FROM emp WHERE ename = 'allen ');

Multi-row Single-Column subquery

 

Three operators are used: IN, ANY, and ALL. IN operation

Example 8,Query all the employees with the same minimum wage in each department

-- Query all employees with the same minimum wage IN each department. SELECT * FROM emp eWHERE e. sal IN (select min (sal) FROM emp group by deptno );

Example 9,Query all employees whose minimum wage is not the same as the minimum wage in each department

-- SELECT * FROM emp eWHERE e. sal not in (select min (sal) FROM emp group by deptno) for all employees whose minimum wage is NOT the same as that IN each department );

ANY can be used in the following three forms:
= ANY: Compares with each element IN the subquery. The function is similar to IN (however, <> ANY is NOT equivalent to not in)
> ANY: it is smaller than the minimum result returned by the subquery (it also contains> = ANY)
<ANY: it is smaller than the maximum number of returned results in the subquery (it also contains <= ANY)

Example 10,Query the salaries of each department manager.

-- Query the salary of each department MANAGER. SELECT * FROM emp WHERE sal = ANY (select min (sal) FROM emp WHERE job = 'manager' group by deptno );

Example 11,The result shows that the salary of each department is higher than that of the manager.

-- SELECT * FROM emp WHERE sal> ANY (select min (sal) FROM emp WHERE job = 'manager' group by deptno) for the salaries of each Department greater than the MANAGER );

Example 12,The result shows that the salary of each department is smaller than that of the manager.

-- SELECT * FROM emp WHERE sal <ANY (select min (sal) FROM emp WHERE job = 'manager' group by deptno) for the salaries of each department smaller than the MANAGER );

The ALL operator has the following usage:
<> ALL: equivalent to not in (but = ALL is NOT equivalent to IN)
> ALL: it is larger than the maximum value in the subquery (it also contains> = ALL)
<ALL: smaller than the minimum value in the subquery (including <= ALL)

Example 13,The result shows that each department is not equal to the manager's salary.

-- SELECT * FROM emp WHERE sal <> ALL (select min (sal) FROM emp WHERE job = 'manager' group by deptno) for the salaries of different departments not equal to the MANAGER );

Example 14,

SELECT  * FROM emp WHERE sal < ALL (   SELECT MIN (sal)  FROM emp  WHERE job='MANAGER'  GROUP BY deptno);

Example 15,

SELECT  * FROM emp WHERE sal >ALL (   SELECT MIN (sal)  FROM emp  WHERE job='MANAGER'  GROUP BY deptno);

NULL data judgment provides an exists structure in SQL to determine whether a subquery returns data. If data is returned in the subquery, The exists structure returns true, and the reverse returns false.

Example 15,Verify the exists Structure

 

-- Verify the exists Structure SELECT * FROM emp where exists (-- return null value, NO content output SELECT * FROM emp WHERE empno = 9999); -- employees without this number

Example 16,

SELECT * FROM emp where exists (SELECT * FROM emp); -- if there is content, data will be returned.

Example 17,

SELECT * FROM emp where not exists (SELECT * FROM emp); -- there is data, but return, no content output