sql关联查询和连接查询

sql 关联查询和连接查询2010-10-26 14:20:02| 分类：服务器 | 标签：联接字号大中小 订阅本文来自 CSDN 博客： 1 取得表中第 6 到第 10 条记录的值1.1 第一种方法，使用minus 语句假设 ddl 语句如下：CREATE TABLE T(ID VARCHAR2(4) PRIMARY KEY, VALUE INT)那么第一种方法就是取出前5 条，再取出前 10 条，然后采用集合运算的方法把前 10 条减去前 5 条就 OK 了， SQL 语句如下以下是引用片段：SELECT * FROM T WHERE ROWNUM = 10MINUSSELECT * FROM T WHERE ROWNUM = 5;1.2 另外一种方法，采用子查询子查询的这种方法相对比较复杂一点，不过性能要比刚才的集合相减要好一些。这种方法首先在子查询中得到前10 条数据，顺路也取得前10 条数据的rownum，然后再一次查询的时候取得刚才查询的rownum 大于 5 的那些数据。SQL 语句如下以下是引用片段：SELECT ID, VALUE FROM(SELECT ID, VALUE, ROWNUM R FROM T WHERE R 5;通过上面的语句，就得到了6 到第 10 条数据了。2 利用外连接替代 not in 语句in 语句还有 not in 语句的效率是非常的差的，因为数据库在遇到这两种语句的时候是要把数据进行一条一条的比对，如果 in 或者 not in 两侧的数据量在上万条的时候，进行比对的次数就是上亿次，很可能一个简单的 sql 语句就要执行半个小时以上。这种效率客户是肯定不能够接受的。那我们可以考虑两种方法进行替代，第一种就是采用 exist 语句和 not exist 语句，这种大家应该比较熟悉了。另外一种就是巧用外关联语句，这种方法可能大家不是很熟悉，我来稍微说一下。假设数据表的建表 DDL 语句为 CREATE TABLE T1(ID VARCHAR2(4) PRIMARYKEY, VALUE INT)而 in 或者 not in 的表的建表 DDL 语句为CREATE TABLE T2(VALUE INT)Oracle 中外关联采用的是 (+)符号表示外关联，也就是说标识了 (+)符号的部分在找不到对应的值的时候为 NULL 。下面是替代 in 语句的时候的 SQL 语句以下是引用片段：SELECT T1.ID, T1.VALUEFROM T1, T2WHERE T1.VALUE = T2.VALUE(+)AND T2.VALUE IS NOT NULL;而类似的。替代not in 语句的时候的以下是引用片段：SQL 语句则为SELECT T1.ID, T1.VALUEFROM T1, T2WHERE T1.VALUE = T2.VALUE(+)AND T2.VALUE IS NULL;大家可以试验一下，在数据量多的时候，采用外关联比用in 或者not in 的执行效率要高很多很多。数据库的左连接，右连接问题前一阶段经历了几次程序员的面试，发现数据库这个部分占了很大的比重。而左连接，右连接又是很多人问的重点，当初我就是不太明白怎么一回事，所以吃了几次的亏。今天把专门作了一次关于左连接和右连接的文章，巩固一下知识：要点： left join,rightjoin,inner join首先有如下两个表：Student：ID （int ） Name(nvarchar)1 a2 b3 c4 d5 e6 f Quiz:ID(int) score(int)1 602 704 806 908 1009 30内连接：（ inner join ）包括连接表的匹配行select Student.Name,Quiz.score from Quiz inner join Student on Student.ID=Quiz.ID Name scorea 60b 70d 80f 90左连接：（ left join ）包括连接表匹配行以及左连接表的所有行select Student.Name,Quiz.score from Student left join Quiz on Student.ID=Quiz.ID Name scorea 60b 70c nulld 80e nullf 90右连接： (right join) 结果包括连接表的匹配行以及右连接表的所有行select Student.Name,Quiz.score from Student right join Quiz on Student.ID=Quiz.ID Name scorea 60b 70d 80f 90 null 100null 30当然，也可以看出左连接也可以写成右连接的形式：select Student.Name,Quiz.score from Student right join Quiz on Student.ID=Quiz.ID等价于select Student.Name,Quiz.score from Quiz left join Student on Student.ID=Quiz.ID 使用外联接仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。Microsoft?SQL Server? 2000 对在 FROM 子句中指定的外联接使用以下SQL-92 关键字：LEFT OUTER JOIN 或 LEFT JOINRIGHT OUTER JOIN 或 RIGHT JOINFULL OUTER JOIN 或 FULL JOINSQL Server 支持 SQL-92 外联接语法，以及在 WHERE 子句中使用 *= 和 =* 运算符指定外联接的旧式语法。由于 SQL-92 语法不容易产生歧义，而旧式 Transact-SQL 外联接有时会产生歧义，因此建议使用SQL-92 语法。使用左向外联接假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者（本例中为 Abraham Bennet和 Cheryl Carson）。若要在结果中包括所有的作者，而不管出版商是否住在同一个城市，请使用 SQL-92 左向外联接。下面是 Transact-SQL 左向外联接的查询和结果： USE pubsSELECT a.au_fname, a.au_lname, p.pub_nameFROM authors a LEFT OUTER JOIN publishers pON a.city = p.cityORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC 下面是结果集：au_fname au_lname pub_name- - -Reginald Blotchet-Halls NULLMichel DeFrance NULLInnes del Castillo NULLAnn Dull NULLMarjorie Green NULLMorningstar Greene NULLBurt Gringlesby NULLSheryl Hunter NULLLivia Karsen NULLCharlene Locksley NULLStearns MacFeather NULLHeather McBadden NULLMichael OLeary NULLSylvia Panteley NULLAlbert Ringer NULLAnne Ringer NULLMeander Smith NULLDean Straight NULLDirk Stringer NULLJohnson White NULLAkiko Yokomoto NULLAbraham Bennet AlgodataInfosystemsCheryl Carson AlgodataInfosystems(23 row(s) affected)不管是否与 publishers 表中的 city 列匹配， LEFT OUTER JOIN 均会在结果中包含authors 表的所有行。注意：结果中所列的大多数作者都没有相匹配的数据 ,因此，这些行的 pub_name列包含空值。 使用右向外联接假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者（本例中为 Abraham Bennet和 Cheryl Carson）。 SQL-92 右向外联接运算符 RIGHT OUTER JOIN 指明：不管第一个表中是否有匹配的数据，结果将包含第二个表中的所有行。若要在结果中包括所有的出版商，而不管城市中是否还有出版商居住，请使用 SQL-92 右向外联接。下面是 Transact-SQL 右向外联接的查询和结果： USE pubsSELECT a.au_fname, a.au_lname, p.pub_nameFROM authors AS a RIGHT OUTER JOIN publishers AS p ON a.city = p.cityORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC 下面是结果集：au_fname au_lname pub_name- - -Abraham Bennet AlgodataInfosystemsCheryl Carson AlgodataInfosystemsNULL NULL Binnet&HardleyNULL NULL Five Lakes PublishingNULL NULL GGG&GNULL NULL Lucerne PublishingNULL NULL New Moon BooksNULL NULL Ramona PublishersNULL NULL Scootney Books(9 row(s) affected)使用谓词（如将联接与常量比较）可以进一步限制外联接。下例包含相同的右向外联接，但消除销售量低于 50 本的书籍的书名：USE pubsSELECT s.stor_id, s.qty, t.titleFROM sales s RIGHT OUTER JOIN titles tON s.title_id = t.title_idAND s.qty 50ORDER BY s.stor_id ASC下面是结果集：stor_idqty title- - -(null) (null) But Is It User Friendly?(null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior Variations(null) (null) Cooking with Computers: Surreptitious Balance Sheets(null) (null) Emotional Security: A New Algorithm(null) (null) Fifty Years in Buckingham Palace Kitchens 7066 75 Is Anger the Enemy?(null) (null) Life Without Fear(null) (null) Net Etiquette(null) (null) Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean(null) (null) Prolonged Data Deprivation: Four Case Studies(null) (null) Secrets of Silicon Valley(null) (null) Silicon Valley Gastronomic Treats(null) (null) Straight Talk About Computers(null) (null) Sushi, Anyone?(null) (null) The Busy Executives Database Guide(null) (null) The Gourmet Microwave(null) (null) The Psychology of Computer Cooking(null) (null) You Can Combat Computer Stress!(18 row(s) affected)有关谓词的更多信息，请参见WHERE 。使用完整外部联接若要通过在联接结果中包括不匹配的行保留不匹配信息，请使用完整外部联接。 Microsoft?SQL Server? 2000 提供完整外部联接运算符 FULL OUTER JOIN ，不管另一个表是否有匹配的值，此运算符都包括两个表中的所有行。假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者（本例中为 Abraham Bennet和 Cheryl Carson）。 SQL-92 FULL OUTER JOIN 运算符指明：不管表中是否有匹配的数据，结果将包括两个表中的所有行。若要在结果中包括所有作者和出版商，而不管城市中是否有出版商或者出版商是否住在同一个城市，请使用完整外部联接。下面是 Transact-SQL 完整外部联接的查询和结果：USE pubsSELECT a.au_fname, a.au_lname, p.pub_nameFROM authors a FULL OUTER JOIN publishers pON a.city = p.cityORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC 下面是结果集：au_fname au_lname pub_name- - -Reginald Blotchet-Halls NULLMichel DeFrance NULLInnes del Castillo NULL Ann Dull NULL Marjorie Green NULL Morningstar Greene NULL Burt Gringlesby NULL Sheryl Hunter NULL Livia Karsen Charlene Locksley Stearns MacFeather Heather McBadden Michael OLeary Sylvia Panteley Albert Ringer Anne Ringer Meander Smith Dean Straight Dirk StringerJohnson White Akiko Yokomoto Abraham Bennet Cheryl Carson NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL(30 row(s) affected) NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL AlgodataInfosystems AlgodataInfosystems Binnet&Hardley Five Lakes Publishing GGG&G Lucerne Publishing New Moon Books Ramona Publishers Scootney Books