2023年6月27日发(作者：)

SQL⼦查询的⽤法SQL ⼦查询的⽤法⼦查询是⼀个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它⼦查询中。任何允许使⽤表达式的地⽅都可以使⽤⼦查询。⼦查询可以使我们的编程灵活多样，可以⽤来实现⼀些特殊的功能。但是在性能上，往往⼀个不合适的⼦查询⽤法会形成⼀个性能瓶颈。如果⼦查询的条件中使⽤了其外层的表的字段，这种⼦查询就叫作相关⼦查询。相关⼦查询可以⽤IN、NOT IN、关于相关⼦查询，应该注意：A、NOT IN、NOT EXISTS的相关⼦查询可以改⽤LEFT JOIN代替写法。⽐如：SELECT PUB_NAME

FROM PUBLISHERS

WHERE PUB_ID NOT IN

(SELECT PUB_ID

FROM TITLES

WHERE TYPE = 'BUSINESS')

可以改写成：SELECT _NAME

FROM PUBLISHERS A LEFT JOIN TITLES B

ON = 'BUSINESS' AND

_ID=_ID

WHERE _ID IS NULL

SELECT TITLE

FROM TITLES

WHERE NOT EXISTS

(SELECT TITLE_ID

FROM SALES

WHERE TITLE_ID = _ID)可以改写成：SELECT TITLE

FROM TITLES LEFT JOIN SALES

ON _ID = _ID

WHERE _ID IS NULLB、 如果保证⼦查询没有重复 ，IN、EXISTS的相关⼦查询可以⽤INNER JOIN 代替。⽐如：SELECT PUB_NAME

FROM PUBLISHERS

WHERE PUB_ID IN

(SELECT PUB_ID

FROM TITLES

WHERE TYPE = 'BUSINESS')可以改写成：SELECT DISTINCT _NAME

FROM PUBLISHERS A INNER JOIN TITLES B

ON = 'BUSINESS' AND

_ID=_ID　　C、 IN的相关⼦查询⽤EXISTS代替，⽐如SELECT PUB_NAME

FROM PUBLISHERS

WHERE PUB_ID IN

(SELECT PUB_ID

FROM TITLES

WHERE TYPE = 'BUSINESS')可以⽤下⾯语句代替：SELECT PUB_NAME

FROM PUBLISHERS

WHERE EXISTS

(SELECT 1

FROM TITLES

WHERE TYPE = 'BUSINESS' AND

PUB_ID= _ID)　　D、不要⽤COUNT(*)的⼦查询判断是否存在记录，最好⽤LEFT JOIN或者EXISTS，⽐如有⼈写这样的语句：SELECT JOB_DESC FROM JOBS

WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=_ID)=0应该改成：SELECT _DESC FROM JOBS LEFT JOIN EMPLOYEE

ON _ID=_ID

WHERE _ID IS NULL

SELECT JOB_DESC FROM JOBS

WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=_ID)<>0应该改成：SELECT JOB_DESC FROM JOBS

WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=_ID)