子查询：在一个SQL语句中嵌套另一个SQL语句
【实例】
--查询商品类别为“图书”的所有商品的id，商品名称以及价格
select id , name ,price from es_product
where sort_id = (
select id from es_sort where sortname = '图书'）；

--查询大于商品平均价格的商品ID 、商品名称以及价格
select id, name, price
from es_product
where price > (select avg(price) 商品平均价格 from es_product);

--查询1号订单下所有商品的id 、商品名称以及价格
select id, name, price
from es_product
where id in (select prod_id from es_orderdetail where order_id = 1);

--查询商品列表中的前5条商品的id 、商品名称以及上架日期
select id, name, saledate
   from es_product
where rownum <=5;
（rownum是oracle数据库中的伪列，它总是从1开始，物理上并不真实存在。所以它不属于任何基表）

--查询商品列表中最新上架的前5条商品的id 、商品名称以及上架时间
--当有排序时，必须先排序，把排序后的表当做虚拟表，在使用rownum去截取n条记录。
select id, name, saledate, rownum
   from (select * from es_product order by saledate desc)
where rownum <= 5;

--分页显示商品id 、商品名称、上架时间，每页显示4条。
--编写查询第二页商品信息的sql语句。
select id, name, saledate from (select id, name, saledate, rownum rn from es_product) a
where a.rn>=5 and a.rn<=8;
--由于rownum的值在执行到where条件时，是动态变化的，这里将rownum的值固化到 rn中。

--按日期降序，分页显示商品id 、商品名称、上架时间。每页显示4条
--编写查询第二页商品信息的sql语句。
select id, name, saledate
  from (select id, name, saledate, rownum rn
          from (select * from es_product
           order by saledate desc)) a
where a.rn>=5 and a.rn<=8;
--内连接：等值连接（表连接）
--查询商品类别为“图书”的所以商品的id 、商品名称、商品价格以及商品分类名称
select p.id, p.name, p.price, s.sortname from es_product p, es_sort s
where s.id = p.sort_id and s.sortname = '图书';

--内连接：非等值连接
--查询所有单价介于20-35元之间的商品名称、价格、库存量、商品类别名称
select p.name, p.price, p.stockcount, s.sortname from es_product p,es_sort s
where s.id=p.sort_id and p.price<35 and p.price>20;

内连接（仅包含那些满足连接条件的记录）：等值连接、非等值连接
等值连接：在连接条件中使用等于（=）运算符比较被连接的值，也就是通过相等的列值连接起来的查询
非等值连接：在连接条件中使用除等号以外的其他比较运算符，如>、<、>=、<=、!=(<>)、like、in、between等
左外连接语法：
select 多表中要查询的列 from 左表 left [outer] join 右表 on 多表连接的条件
【实例】
--查询所有用户的用户姓名、电话、订单号、订单日期以及订单状态
select u.realname 姓名, u.tel 电话, o.id 订单号, o.createtime 订单日期, o.status 订单状态 from es_user u,es_order o
where u.id=o.user_id(+);
--使用子查询和连接查询，编写sql语句，查询出高于本类产品平均价格的商品信息，
--包括商品类别id 、商品id 、商品名称、价格以及库存量
select a.sort_id, a.id, a.name, a.price, a.stockcount
  from es_product a,
       (select sort_id, avg(price) avgprice from es_product group by sort_id) b
where a.sort_id = b.sort_id
   and a.price > b.avgprice
【实例】union、intersect、minus的使用
--获得一次订单满200元或订单累计满500元的用户：
select user_id
  from es_order
where totalamount > 200
union
select user_id from es_order group by user_id having sum(totalamount) > 500

--获取一次订单满200元并且订单累计满500元的用户；
select user_id
  from es_order
where totalamount > 200
intersect
select user_id from es_order group by user_id having sum(totalamount) > 500

--获取没有被订购过的商品id ；
select id from es_product
minus
select prod_id from es_orderdetail;

总结：1、union和union all 都可以用在集合查询的“并集”操作，不过union all 比union效率高，因为union查询出来的内容提剔除了重复行，同时默认按第一个查询的第一列升序排序。而union all不做这些，在业务要求允许时，最好使用效率高的union all
2、使用union  union all  intersect  minus 连接的两个select语句中，查询的列数和列的数据类型要一致。
3、union在做重复行检查时，不忽略空值（null），如果有重复空行，则保留一个空行，intersect 以及minus 查询也无重复行，按第一个查询的第一列升序排列

ITPub博客

SQL高级查询