SQL语句的良好编写方法讲解

1、SQL语句的良好编写方法Author: InthirtiesDate: 2008-11-211 .涉及到多个表的 JOIN语句的写法很多开发人员对于涉及到多个表的JOIN语句，不知道如何下手，以及攵?何写出一个效率好的JOIN语句来，在这里，我根据我的一些经验，给出一些建议供大家参考：(1) 了解一个SELECT语句的结构 通常，一个SELECT吾句的结构是这样的： SELECT select_column_list step_4FROM table_list step_1WHEREjoin_condition step_2AND predicates step_3ORDER BYorderb

2、y_clause step_5(2) SELECT语句的书写顺序通常情况下，SELECT语句的结构都是如我上面所描述的那样，大家在写SQL语句时，不妨先写好框架，如：先写SELECT语句的一些关键字：SELECT *FROMWHEREORDER BY框架写好后，我们再逐步加精.书写的顺序按上图我标的顺序：第一步，先写查询所要涉及到的表，不管有多少个表，我们先把表名列上去，为了后面语句的简洁，可以给表加上别名，如果有同一个表要使用几次，则要使用不同的别名来标识，或者容易产生岐义：SELECT *FROM pm_user a, pm_companyo -pm_user c WHEREORDER B

3、Y写好table_list后，我们开始来定义 JOIN的连接条件，注意，对于涉及到多个表的连接，我们一定要避免不定义连接条件的情况，因为这会产生迪卡尔连接(Cartesian Joins)SELECT *FROM pm_user a, pm_company)pm_user c .表歹U表 WHEREpid=pid 连接条件ORDER BY当所有的连接条件都定义好了以后，我们要再检查一下，看是否有其中的两个表完全没有定义连接条件的，这在JOIN的表很多的情况下，大家很容易犯的错误。一个好的检测方法是：书写好SQL后，在pl/sql developer 中按F5键查看执行计划，当SQL的执行计划中

4、有出现关键字为Cartesian 的操作时，就表示我们不小心产生了一个迪卡尔连接，要回去再REVIEW一下我们定义的连接条件 写好连接条件后，接下来，我们就可以在WHEREF句中接着写过滤条件 (我们把它称之为 predicates), 即有具体值，能过滤掉一部分记录的 WHER康件。通常，良好的编写习惯是将过滤条件写在WHEREF句的最下部，这样，SQL引擎在对表进行连接操作时，会先过滤掉参与连接的表的一部分记录，这样，参与连接的数据集就会少一点，从而连接操作的成本也 小一点：SELECT *FROM pm_user a, pm_company)pm_user c .表列表WHEREpid=

5、pid 连接条件And a.userid=123456 and .过滤条件ORDER BY写好过滤条件后，基本上 WHEREF句就写完了，接着我们就可以来定义查询字段列表了(select_column_list),即查询执行完后我们想要看到的字段。如果有同名的字段，可以定义字段别名(如下例中的和)SELECT a.userid, as username, as company_name查询字段歹U表FROM pm_user a, pm_company)pm_user c .表歹U表WHEREpid=pid 连接条件And a.userid=123

6、456 and .过滤条件ORDER BY定义ORDERLY子句。ORDEfBY即排序字段。因为排序操作通常是一个SELECT语句的最后一步操作，所以，通常， ORDERBY子句也可以放到最后来写，如果查询字段列表中有定义别名，排序字段中也可以使用表名(如下例中的username):SELECT a.userid, as username, as company_name查询字段歹U表FROM pm_user a, pm_company)pm_user c .表歹U表 WHEREpid=pid 连接条件And a.userid=123456 and .过滤条件ORDER

7、 BYa.userid,username .排序字段经过上面这些步骤，基本上，一个SELECT语句就完成了。(3) 良好性能JOIN语句的写法下面介绍一下如何写一个执行性能良好的JOIN语句尽量写平级的连接，不要写嵌套的连接什么是平级的连接呢？平级的连接是指连接的表都在同一级的查询结构，如下例的表，即为平级的连接：SELECT a.userid, as username, as company_name查询字段歹U表FROM pm_user a, pm_company)pm_user c .表歹U表WHEREpid=pid 连接条件And a.userid=123456

8、 and .过滤条件ORDER BYa.userid,username .排序字段表pm_user,pm_company 等都位于同一级的查询结构。那么，什么又是嵌套的连接呢？嵌套的连接同常出现在有子查询的SELECT语句中，子查询中的表有连接到外部查询(或者说父查询)的表，如下例中,tab1和tab3的连接即为一个嵌套的连接： SELECT * FROM tabi ,tab2 WHERE tabl.col in (select tab3.col fromtab3 where tab3.col2=tab1.col2 )还有一种嵌套连接的例子出现在select_column_list ,请看下面

9、的例子：SELECT tabl.col,(select tab3.col from tab3 where tab3.col2=tab1.col2 ) as col_aliasFROM tabi ,tab2 WHERE tab1.col=tab2.col此例中，标红色的部分，即 tabi和tab3也是一个嵌套连接。这种格式在ORACLE中称之为标量子查询表达式(Scalar SubqueryExpressions ,即一个子查询只能返回一行值的子查询)。通常情况下，嵌套连接的成本要比平级连接的成本要大。这好比我们写程序中的多层循环语句，代价是OutLoop*InnerLoop,所以，在ORACL

10、E,有专门的针对嵌套子查询，或嵌套连接的优化方法，叫做展平(Unnesting of Nested Subqueries or Nested Joins),或者说解嵌套。如果不可避免的会有用到嵌套连接，则尽量使用EXISTS,而不要使用IN比如下面的语句：SELECT *tab3 where tab3.col2=tabi.col2FROM tabi ,tab2 WHERE tabi.col in (select tab3.col from如果我们的业务逻辑不可避免，一定要用到嵌套连接，ORACLE中也把这种查询叫做关联子查询(Correlated Subqueries), 则我们通常可以用EX

11、ISTS来实现，而不用IN来实现。当然，这是指通常的情况，而不是绝对。我们也不是说EXISTS一定比IN效率高，但根据我个人的情况，大部分情况下，使用EXISTS会比IN效果好，所以上面的语句可以这样实现：SELECT *FROM tabi ,tab2 WHERE EXISTS (selectX from tab3 where tab3.col1=tab1,col1 )那么，为什么在嵌套连接中EXISTS通常会比IN效率好呢？嵌套连接好比是2个嵌套的循环，对于外部循环中的每一行，都要到内部循环中去搜索(或者说遍历)一遍，EXISTS和IN的差别就在于，EXISTS在内部循环中只到找到第1行，它

12、就跳出内部循环，没有必要继续往下搜索，这样就结省了一部分搜索内部循环的成本。而IN呢？ IN的情况是，即使是在内部循环中找到1行，它也会继续搜索下去，直到遍历完内部循环的全部记录。我用下图来解释一下：COST=m*1COST=m*n 何时用EXISTS,何时用IN, ORACLEl一个建议，大家可以参考一下：如果在子查询中有包含选择性的谓词(过滤器filter),并且在这个谓词字段或连接字段(join column)上有建索引，则使用IN如果在父查询中包含选择性的谓词(过滤器filter), 则采用EXISTS我们来看下面这个 SQL:SELECT * FROM departmentsWHER

13、E EXISTS(SELECT * FROM employeesWHERE departments.department_id = employees.department_idAND employees.salary 2500);如果employee的salary 这个字段的差异值比较多 (即具有很多不同的字段值 ，这表示选择性selectivity 比较好)，并且salary 字 段有建索引，很显然，我们应该采用IN:SELECT*FROMDEPARTMENTSWHERDEPARTMENT_IDIN(SELECTDEPARTMENT_IDFROMEMPLOYEESWHER EMPLOYEE

14、S.SALARY* 2500);如果salary字段具有很多相同的字段值即选择性不好 ，且没有建索引，则采用EXISTS性能会好一些.或者假如department这个表多 了一个谓词，且这个谓词很具选择性 ，则采用EXISTS,这样，在评估EXISTS里面的子查询时，外部的父查询先可以应用这个谓词过滤 一些数据，这样就减少了 EXISTS内部子查询所需要访问的数据量.IN 和EXISTS在ORACL咛都称之为半连接(semi-join ).半连接的意思可能是这么来的，查询只能返回(即SELECTLIST) IN/EXISTS外部表的字段，它消除了连接中来自内表 (即IN或EXISTS里面的表)

15、的冗余值。所以，IN和EXISTS在某些情况下也都能达到“去重”的目的，但是 ORACLEI理IN和EXISTS时所采用的方法还是不同的，以下面的SQL为例：SELECTd. *FROMdepartments dWHERE.department_id IN (SELECT*+ NO_UNNEST */ e.department_idFROgmployees e )如果ORACL目尤化器并没将这个子查询展平成连接，则针对in的外部表DEPARTMENTS每一行，ORACL嘟会执行一下子查询 SELECTe.department_idFRO岫mployees e ,并且将子查询返回的 depart

16、ment_id进行排序和去除重复值，然后拿 departments 的department_id 与子查询返回的结果集进行比较，如果是该结果集的成员，则查询返回外部表的记录。所以IN比EXISTS多了一个成本：排序去重。所以要将IN改写成JOIN的话，先要将IN内表作一个排序去重的操作(加上DISTINCT),上面的SQL改写成JOIN就是下面这个样子：SELECTd. *FROMdepartments d ,( SELECT DISTINCT department_idFROMsmployees ) eWHERE.department_id =e.department_id2 .用 Wher

17、e 子句替换 GROUP BY 的HAVING 子句因为GROUP BY的HAVING子句是在对数据进行分组后才对结果集进行过滤，这个处理需要排序，汇总合计等操作，所以如果能通过WHER子句限制需要进行分组 (grouping)的记录的数目，那就能减少这方面的开销 ，我们来看下面的例子：低效：SELECT REGION , AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION REGION !=SYDNEYAND REGION != PERTH高效SELECT REGION , AVG(LOG_SIZE)FROM LOCATIONWHER

18、E REGION REGION !=SYDNEYAND REGION != PERTHGROUP BY REGION我们要尽量把过滤条件写在where子句中，而不是写在having子句中，这是一条非常实用的原则 .3 .在有用至1J union,minus,intersect,group by 等 sql 语句中不使用 distinct 和 order by因为union,minus,intersect,group by已经会对相关的字段进行排序和去除重复值，所以就没有必要在这些SQL语句中再使用distinct 和order by 子句，如下面的SQL语句：SELECT distinctLO

19、CATION_ID, DEPARTMENT_NAME Department, TO_CHARNULL WarehouseFROMDEPARTMENTSUNIONSELECTdistinct LOCATION_ID, TO_CHARNUL1) Department , WAREHOUSE_NAMEFROMWAREHOUSESOrder by LOCATION_ID上面的SQL中，由于union会对每个查询中的结果集进行排序去从(sort unique)操彳,所以再添加 distinct 和order by location_id是没有必要的.4 .不要在创建有索引的谓词字段上运用函数如果在谓词字

20、段上有运用函数，如类型转换函数(to_char(),to_date(),to_number().)和大小写转换函数 (upper(),lower(), 则既使这些字段上有建索引，ORACLE优化器也不会使用这个索引，除非有建立基于函数的索引(function based index).我们来看下面的例子：SELECT* FROMbm_roleWHERUPPER NAM)= RAINNY上面的SQL中，由于谓词字段：name上有运用一个upper()函数，所以既使我们在 pm_上有建一个索引，Oracle也不会用到 这个索引，所以我们一般要把函数写到等号(=)的右边.5 .不要在创建有索引的谓词字段上运用操作符如果有对谓词字段运用操作符，则ORACL同样不会用到索引，我们来看下面的例子：SELE