带In关键字的子查询
一个查询语句的条件可能落在另一个 SELECT 语句的查询结果中。
SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype); SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);
带比较运算符的子查询
子查询可以使用比较运算符。
SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);
带Exists关键字的子查询
假如子查询查询到记录，则进行外层查询，否则，不执行外层查询；
SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype); SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);
带Any关键字的子查询
ANY 关键字表示满足其中任一条件；
SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);
带All关键字的子查询
ALL 关键字表示满足所有条件；
SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);
UNION
使用UNION关键字是：数据库系统会讲所有的查询结果合并到一起，然后去掉重复
SELECT id FROM t_book UNION SELECT id from t_booktype; SELECT id FROM t_book UNION all SELECT id from t_booktype;
原文：https://www.cnblogs.com/linbin7/p/11930303.html