子查询

带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;