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

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

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

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

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

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

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

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

8、_company> pm_user c .表歹U表WHEREpid=pid 连接条件And a.userid=123456 and . 过滤条件ORDER BYa.userid,username . 排序字段表 pm_user,pm_company 等都位于同一级的查询结构。那么，什么又是嵌套的连接呢？嵌套的连接同常岀现在有子查询的SELECT语句中，子查询中的表有连接到外部查询（或者说父查询）的表，如下例中 ,tab1 和 tab3 的连接即为一个嵌套的连接：SELECT *FROM tab1 ,tab2 WHERE tab1.col in （select tab3.col fromt

9、ab3 where tab3.col2=tab1.col2 ）还有一种嵌套连接的例子岀现在 select_column_list ，请看下面的例子：SELECT tab1.col,（select tab3.col from tab3 where tab3.col2=tab1.col2 ） as col_aliasFROM tab1 ,tab2 WHERE tab1.col=tab2.col此例中，标红色的部分，即tabl和tab3也是一个嵌套连接。这种格式在ORACLE中称之为标量子查询表达式（Scalar SubqueryExpressions ，即一个子查询只能返回一行值的子查询 ） 。通

10、常情况下，嵌套连接的成本要比平级连接的成本要大。这好比我们写程序中的多层循环语句，代价是OutLoop*InnerLoop, 所以，在ORACLE中，有专门的针对嵌套子查询，或嵌套连接的优化方法，叫做展平（Unn esti ng of Nested Subqueries or Nested Joi ns）,或者说解嵌套。<2>如果不可避免的会有用到嵌套连接，则尽量使用EXISTS，而不要使用IN比如下面的语句：SELECT *FROM tab1 ,tab2 tab3 where tab3.col2=tab1.col2WHERE tab1.col in (select tab3.co

11、l from如果我们的业务逻辑不可避免，一定要用到嵌套连接，ORACLE中也把这种查询叫做关联子查询 (Correlated Subqueries), 则我们通常可以用EXISTS来实现，而不用IN来实现。当然，这是指通常的情况，而不是绝对。我们也不是说EXISTS 一定比IN效率高，但根据我个人的情况，大部分情况下，使用EXISTS会比IN效果好，所以上面的语句可以这样实现：SELECT *FROM tabl ,tab2 WHERE EXISTS (select X' from tab3 where tab3.col1=tab1.col1 )那么，为什么在嵌套连接中EXISTS通常会

12、比IN效率好呢？嵌套连接好比是2个嵌套的循环，对于外部循环中的每一行，都要到内部循环中去搜索(或者说遍历)一遍，EXISTS和IN的差别就在于，EXISTS在内部循环中只到找到第1行，它就跳岀内部循环，没有必要继续往下搜索，这样就结省了一部分搜索内部循环的成本。而IN呢？ IN的情况是，即使是在内部循环中找到1行，它也会继续搜索下去，直到遍历完内部循环的全部记录。我用下图来解释一下：COST=m*1COST=m* n<3> 何时用EXISTS，何时用IN , ORACLE个建议，大家可以参考一下：如果在子查询中有包含选择性的谓词(过滤器filter),并且在这个谓词字段或连接字段(

13、join column)上有建索引，则使用IN如果在父查询中包含选择性的谓词(过滤器filter), 则采用EXISTS我们来看下面这个 SQL:SELECT * FROM departme ntsWHERE EXISTS(SELECT * FROM employeesWHERE departme nts.departme nt_id = employees.departme nt_idAND employees.salary > 2500);如果employee的salary 这个字段的差异值比较多(即具有很多不同的字段值 ，这表示选择性 selectivity 比较好)，并且sala

14、ry 字段有建索引，很显然，我们应该采用IN:SELECT*FROMDEPARTMENTSWHERDEPARTMENT_IDIN(SELECTDEPARTMENT IDFROMEMPLOYEESWHEREMPLOYEES.SALARY> 2500);如果salary字段具有很多相同的字段值即选择性不好，且没有建索引，则采用EXISTS性能会好一些.或者假如department这个表多了一个谓词，且这个谓词很具选择性 ，则采用EXISTS,这样，在评估EXISTS里面的子查询时，外部的父查询先可以应用这个谓词过滤 一些数据，这样就减少了 EXISTS内部子查询所需要访问的数据量.IN 和E

15、XISTS在ORACL中都称之为半连接(semi-join ).半连接的意思可能是这么来的， 查询只能返回(即SELECTLIST) IN/EXISTS 外部表的字段，它消除了连接中来自内表 (即IN或EXISTS里面的表)的冗余值。所以，IN和EXISTS在某些情况下也都能达到 “去 重”的目的，但是 ORACL旌处理IN和EXISTS时所采用的方法还是不同的，以下面的SQL为例：SELECTd. *FROMUepartme nts dWHERE.department_idIN (SELECT*+ NO_UNNEST */ e.department_idFROMbmployees e )如果

16、ORACLE尤化器并没将这个子查询展平成连接，则针对in的外部表DEPARTMENT的每一行，ORACL睹0会执行一下子查询SELECTe.departme nt_id FROMfemployees e ，并且将子查询返回的departme nt_id进行排序和去除重复值，然后拿 departme nts 的departme nt_id 与子查询返回的结果集进行比较，如果是该结果集的成员，则查询返回外部表的记录。所以IN比EXISTS多了一个成本：排序去重。所以要将IN改写成JOIN的话，先要将IN内表作一个排序去重的操作(加上DISTINCT)，上面的SQL改写成JOIN就是下面这个样子：S

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

18、 LOCATIONGROUP BY REGIONHAVING REGION REGION 匸'SYDNEYAND REGION != ' PERTH高效SELECT REGION ，AVG(LOG_SIZE)FROM LOCATIONWHERE REGION REGION SYDNEYAND REGION != ' PERTHGROUP BY REGION我们要尽量把过滤条件写在where子句中，而不是写在having子句中，这是一条非常实用的原则.3. 在有用至U union,minus,intersect,group by等 sql 语句中不使用 distinet

19、和口 order by因为union,minus,intersect,group by已经会对相关的字段进行排序和去除重复值，所以就没有必要在这些SQL语句中再使用distinct和order by 子句，如下面的SQL语句：SELECT distinctLOCATION_ID, DEPARTMENT_NAME "Department", TO_CHARNUL1) "Warehouse"FROMDEPARTMENTSUNIONSELECTdistinetLOCATIOND, TO_CHARNULL "Department" , WAR

20、EHOUSE_NAMEFROMWAREHOUSESOrder by LOCATIOND上面的SQL中 ,由于union会对每个查询中的结果集进行排序去从(sort unique)操作，所以再添加 distinct 和order by location_id是没有必要的.4. 不要在创建有索引的谓词字段上运用函数如果在谓词字段上有运用函数，如类型转换函数(to_char(),to_date(),to_number().)和大小写转换函数 (upper(),lower(), 则既使这些字段上有建索引ORACLE优化器也不会使用这个索引，除非有建立基于函数的索引(function based index).我们来看下面的例子：SELECT* FROIpm_roleWHERUPPER NAM)= 'RAINNY'上面的SQL中，由于谓词字段:name上有运用一个upper()函数，所以既使我们在 pm_上有建一个索引Oracle也不会用到 这个索引，所以我们一般要把函数写到等号(=)的右边.5. 不要在创建有索引的谓词字段上运用操作符如果有对谓词字段运用操作符，则ORACLE同样不会用到索引，我们来看下面的例子：SELECT* FROM)m_roleWHERE ' R' | NAM巨'R