Subquery 1 (subquery in WHERE), where in subquery

Source: Internet
Author: User

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

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.