每个子查询都有它所属于的外部查询。
独立子查询是独立于其外部查询的子查询,可以选中子查询部分的代码独立运行。
在逻辑处理过程中,子查询的位置决定了他被执行的顺序
SELECT a.employee_name, b.salary_type ,
(b.salary-(SELECT AVG(salary) FROM salary where salary_type ='岗位工资')) AS '差距'
FROM employee a
INNER JOIN salary b
ON a.employee_id = b.employee_id
WHERE b.salary_type ='岗位工资'
SELECT AVG(salary) FROM salary where salary_type ='岗位工资'
上述语句是独立子查询,可以独立运行且不依赖外部查询的返回的结果
标量子查询是返回单个值的子查询
SELECT a.employee_name, SUM(b.salary)
FROM employee a
INNER JOIN salary b
ON a.employee_id = b.employee_id
WHERE b.salary_type ='岗位工资' and
b.salary <(SELECT AVG(salary) FROM salary where salary_type ='岗位工资')
group by a.employee_name
执行下列语句,更新试验环境
ALTER TABLE employee
ADD age int
GO
UPDATE employee SET AGE = 26 WHERE employee_id =1
UPDATE employee SET AGE = 33 WHERE employee_id =2
UPDATE employee SET AGE = 45 WHERE employee_id =3
UPDATE employee SET AGE = 67 WHERE employee_id =4
UPDATE employee SET AGE = 54 WHERE employee_id =5
UPDATE employee SET AGE = 18 WHERE employee_id =6
SELECT * FROM employee
where age > (SELECT age FROM employee where age>30)
如果使用单值比较运算符和子查询进行比较,子查询如果返回多个值或多个列会报错
可以使用IN、EXISTS、 ALL、ANY或SOME关键字,其中SOME是和ANY等效的ISO标准。
SELECT age FROM employee where age>30
执行上述语句结果
SELECT * FROM employee
where age > ANY(SELECT age FROM employee where age>30)
上述语句ANY 只要大于 子查询中任一一个年龄的员工信息都会显示(大于33岁)
SELECT * FROM employee
where age >= ALL(SELECT age FROM employee where age>30)
上述语句ALL 只要大于或等于 子查询中所有年龄的员工信息都会显示(大于67岁)
当子查询中包含 NULL 时,使用ALL、ANY 时不返回任何值,因为ALL和ANY 实际上等价于使用了 AND 或者OR连接了条件,与NULL 进行比较的本质并没有改变。
学习参考资料:《跟韩老师学 SQL Server 数据库设计与开发》
页面更新:2024-04-25
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2020-2024 All Rights Reserved. Powered By 71396.com 闽ICP备11008920号-4
闽公网安备35020302034903号