Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use sub-query to retrieve data in mysql". In daily operation, I believe many people have doubts about how to use sub-query to retrieve data in mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use sub-query to retrieve data in mysql". Next, please follow the editor to study!

1. Related subqueries

The related subquery has a result corresponding to each value of the external query, and the calculation process is as follows:

1. Scan the first record of the external query

2. Scan the subquery and pass the corresponding value of the first record to the subquery, from which the result of the subquery is calculated.

3. Based on the results of the subquery, return the results of the external query.

4. Repeat the above actions and start scanning the second record and the third record of the external query until all of them have been scanned.

Example:

-- inquire about the highest-paid employees in the department?

Method 1, use nested subqueries (non-associative subqueries)

Select * from emp a where (a.deptnoma.sal) in (select deptno,max (sal) from emp group by deptno)

Method 2, use the associated subquery

Select * from emp a where a.sall = (select max (sal) from emp where deptno=a.deptno)

II. EXISTS

Exists is to judge whether the sql statement after exits is true, and if it is true, the whole sql sentence is true, otherwise there is no record.

Example:

SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM DUAL WHERE 2 = 1)

III. WITH

Syntax:

SELECT *

FROM (--simulates the generation of 20 rows of data

SELECT LEVEL AS lv

FROM DUAL

CONNECT BY LEVEL

< 20) tt WHERE tt.lv >

10 AND tt.lv

< 15 WITH TT AS(--模拟生一个20行的数据 SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20) SELECT lv FROM TT WHERE lv >

10 AND lv

< 15 WITH tempName AS (SELECT ....) SELECT ... 说明： 　　可认为在真正进行查询之前预先构造了一个临时表TT，之后便可多次使用它做进一步的分析和处理 优点： 　　增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；更重要的是："一次分析，多次使用"，这也是为什么会提供性能的地方，达到了"少读"的目标。 例句： --普通查询 SELECT * FROM (--模拟生一个20行的数据 SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20) tt WHERE tt.lv >

10 AND tt.lv

< 15; --with语句 WITH TT AS(--模拟生一个20行的数据 SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20) SELECT lv FROM TT WHERE lv >

10 AND lv < 15

At this point, the study on "how mysql uses subqueries to retrieve data" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.