--单行子查询
select first_name,last_name from customers
where customer_id=(select customer_id from customers where last_name='Brown');
--John Brown

select product_id,name,price from products
where price>(select avg(price) from products);
2 Chemistry 30
3 Supernova 25.99

select * from products;
select product_type_id,avg(price) from products
group by product_type_id
having avg(price)<(select max(avg(price)) from products
group by product_type_id);

select max(avg(price)) from products
group by product_type_id
24.975

select prds.product_id,price,purchases_data.product_count
from products prds,
(select product_id,count(product_id) product_count from purchases
group by product_id) purchases_data
where prds.product_id=purchases_data.product_id;
--从product表中检索出 product_id 与 price列再从另外一个表中检索出购买产品的次数

--单行子查询不能使用order by

单行子查询最多返回一行（如果子查询返回多行就会出现如下错误）
select product_id,name from products
where product_id=(SELECT PRODUCT_ID FROM PRODUCTS
WHERE NAME LIKE '%e%');--使用等于两边内容并不等所以就产生了错误
01427. 00000 - "single-row subquery returns more than one row"

改成 in 则变为多行子查询便不会出错
select product_id,name from products
where product_id in (SELECT PRODUCT_ID FROM PRODUCTS
WHERE NAME LIKE '%e%');

(not in)
select product_id,name from products
where product_id not in (SELECT PRODUCT_ID FROM PRODUCTS
WHERE NAME LIKE '%e%');

any操作符
select salary from employees;
800000
600000
150000
500000
select low_salary from salary_grades;
1
250001
500001
750001

select employee_id,last_name,salary from employees
where salary< any (select low_salary from salary_grades);
3 Hobbs 150000
4 Jones 500000
2 Johnson 600000

all操作符
select employee_id,last_name,salary from employees
where salary> all (select high_salary from salary_grades);
no rows selected

多列子查询
select product_id,product_type_id,name,price
from products
where (product_type_id,price) in
(select product_type_id,min(price) from products group by product_type_id);
1 1 Modern Science 19.95
4 2 Tank War 19.95

关联子查询
关联子查询会引用sql语句中的一列或多列，这种子查询之所以被称为关联子查询
是因为它们通过相同的列与外部的sql语句关联

例
查询检索那些价格高于同类产品平均价格的产品
select product_id,product_type_id,name,price from products outer
where price>(select avg(price) from products
inner where inner.product_type_id=outer.product_type_id);
2 1 Chemistry 30
3 2 Supernova 25.99

外部查询从products表中检索出所有的行，并将其传递给内部查询
内部查询一次读取外部查询传递来的每一行数据，并对内部查询中product_type_id
等于外部查询中product_type_id值得每种产品计算平均价格

在关联查询中使用EXISTS和NOT EXISTS
SELECT EMPLOYEE_ID,LAST_NAME FROM EMPLOYEES OUTER
WHERE EXISTS
(SELECT EMPLOYEE_ID FROM EMPLOYEES INNER
WHERE INNER.MANAGER_ID=OUTER.EMPLOYEE_ID);

1 Smith
2 Johnson

SELECT EMPLOYEE_ID,LAST_NAME FROM EMPLOYEES OUTER
WHERE EXISTS
(SELECT 1 FROM EMPLOYEES INNER
WHERE INNER.MANAGER_ID=OUTER.EMPLOYEE_ID);
1 Smith
2 Johnson

NOT EXISTS
检索从来未曾卖出的产品
select product_id,name from products outer
where not exists
(select 1 from purchases inner
where inner.product_id=outer.product_id);
3 Supernova
4 Tank War

EXISTS与in不同 EXISTS注重检查行的存在性而in则检查实际值的存在性
select * from PRODUCT_types;
1 Book
2 Video
3 DVD
4 CD
5 Magazine
create table product_types_1(id number,name varchar2(20));
insert into product_types_1 select * from product_types;
update product_types_1 set name=' ' where ID<5;

select id,name from product_types_1 outer
where not exists
(select 1 from products inner
where inner.product_type_id=outer.id);

select id,name from product_types_1 outer
where id not in
(select product_type_id from products);

--嵌套子查询嵌套层次最多为255
外部查询
select product_type_id,avg(price) from products
group by product_type_id
having avg(price)<()
子查询
select max(avg(price)) from products where product_type_id in()
嵌套子查询
select product_id from purchases where quantity>1;

ITPub博客

子查询一

最新文章