在很多情况下,遇到select性能问题时,我们可以将相关子查询转化成表连接,这样数据库引擎有可能更方便的得到更优的查询计划,从而使SQL语句的执行时间减少。达到提高查询性能的目的。
子查询
l 相关子查询(correlated subquery)和不相关子查询(non-correlated subquery)。
l 在相关子查询中,子查询中涉及到父查询的数据列;
l 在相关子查询中,子查询中涉及到父查询的数据列;
l 在不相关子查询中,子查询中不涉及到父查询的数据列。
一、相关子查询示例
SELECT * FROM customer WHERE EXISTS (SELECT * FROM vip
WHERE vip.customer_num = customer.customer_num)
二、不相关子查询示例
SELECT * from project WHERE employee_num = ( SELECT employee_num FROM employee where employee_name = ‘Jim Green’ )
三、优化样例
SELECT * FROM customer WHERE EXISTS (SELECT * FROM vip
WHERE vip.customer_num = customer.customer_num);
此语句应该优化为:
SELECT customer.* FROM customer, vip
WHERE customer.customer_num = vip.customer_num;