sql關(guān)聯(lián)查詢和連接查詢

1、sql 關(guān)聯(lián)查詢和連接查詢2010-10-26 14:20:02| 分類：服務(wù)器 | 標(biāo)簽：聯(lián)接字號(hào)大中小 訂閱本文來自 CSDN 博客： 1 取得表中第 6 到第 10 條記錄的值1.1 第一種方法，使用minus 語句假設(shè) ddl 語句如下：CREATE TABLE T(ID VARCHAR2(4) PRIMARY KEY, VALUE INT)那么第一種方法就是取出前5 條，再取出前 10 條，然后采用集合運(yùn)算的方法把前 10 條減去前 5 條就 OK 了， SQL 語句如下以下是引用片段：SELECT * FROM T WHERE ROWNUM <= 10MINUSSELECT

2、* FROM T WHERE ROWNUM <= 5;1.2 另外一種方法，采用子查詢子查詢的這種方法相對(duì)比較復(fù)雜一點(diǎn)，不過性能要比剛才的集合相減要好一些。這種方法首先在子查詢中得到前10 條數(shù)據(jù)，順路也取得前10 條數(shù)據(jù)的rownum，然后再一次查詢的時(shí)候取得剛才查詢的rownum 大于 5 的那些數(shù)據(jù)。SQL 語句如下以下是引用片段：SELECT ID, VALUE FROM(SELECT ID, VALUE, ROWNUM R FROM T WHERE R <= 10)WHERER>5;通過上面的語句，就得到了6 到第 10 條數(shù)據(jù)了。2 利用外連接替代 not in

3、語句in 語句還有 not in 語句的效率是非常的差的，因?yàn)閿?shù)據(jù)庫(kù)在遇到這兩種語句的時(shí)候是要把數(shù)據(jù)進(jìn)行一條一條的比對(duì)，如果 in 或者 not in 兩側(cè)的數(shù)據(jù)量在上萬條的時(shí)候，進(jìn)行比對(duì)的次數(shù)就是上億次，很可能一個(gè)簡(jiǎn)單的 sql 語句就要執(zhí)行半個(gè)小時(shí)以上。這種效率客戶是肯定不能夠接受的。那我們可以考慮兩種方法進(jìn)行替代，第一種就是采用 exist 語句和 not exist 語句，這種大家應(yīng)該比較熟悉了。另外一種就是巧用外關(guān)聯(lián)語句，這種方法可能大家不是很熟悉，我來稍微說一下。假設(shè)數(shù)據(jù)表的建表 DDL 語句為 CREATE TABLE T1(ID VARCHAR2(4) PRIMARYKEY,

4、VALUE INT)而 in 或者 not in 的表的建表 DDL 語句為CREATE TABLE T2(VALUE INT)Oracle 中外關(guān)聯(lián)采用的是 (+)符號(hào)表示外關(guān)聯(lián)，也就是說標(biāo)識(shí)了 (+)符號(hào)的部分在找不到對(duì)應(yīng)的值的時(shí)候?yàn)?NULL 。下面是替代 in 語句的時(shí)候的 SQL 語句以下是引用片段：SELECT T1.ID, T1.VALUEFROM T1, T2WHERE T1.VALUE = T2.VALUE(+)AND T2.VALUE IS NOT NULL;而類似的。替代not in 語句的時(shí)候的以下是引用片段：SQL 語句則為SELECT T1.ID, T1.VALUE

5、FROM T1, T2WHERE T1.VALUE = T2.VALUE(+)AND T2.VALUE IS NULL;大家可以試驗(yàn)一下，在數(shù)據(jù)量多的時(shí)候，采用外關(guān)聯(lián)比用in 或者not in 的執(zhí)行效率要高很多很多。數(shù)據(jù)庫(kù)的左連接，右連接問題前一階段經(jīng)歷了幾次程序員的面試，發(fā)現(xiàn)數(shù)據(jù)庫(kù)這個(gè)部分占了很大的比重。而左連接，右連接又是很多人問的重點(diǎn)，當(dāng)初我就是不太明白怎么一回事，所以吃了幾次的虧。今天把專門作了一次關(guān)于左連接和右連接的文章，鞏固一下知識(shí)：要點(diǎn)： left join,rightjoin,inner join首先有如下兩個(gè)表：Student：ID （int ） Name(nvarchar

6、)1 a2 b3 c4 d5 e6 f Quiz:ID(int) score(int)1 602 704 806 908 1009 30內(nèi)連接：（ 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

7、Student.ID=Quiz.ID Name scorea 60b 70c nulld 80e nullf 90右連接： (right join) 結(jié)果包括連接表的匹配行以及右連接表的所有行select Student.Name,Quiz.score from Student right join Quiz on Student.ID=Quiz.ID Name scorea 60b 70d 80f 90 null 100null 30當(dāng)然，也可以看出左連接也可以寫成右連接的形式：select Student.Name,Quiz.score from Student right join Qu

8、iz on Student.ID=Quiz.ID等價(jià)于select Student.Name,Quiz.score from Quiz left join Student on Student.ID=Quiz.ID 使用外聯(lián)接僅當(dāng)至少有一個(gè)同屬于兩表的行符合聯(lián)接條件時(shí)，內(nèi)聯(lián)接才返回行。內(nèi)聯(lián)接消除與另一個(gè)表中的任何行不匹配的行。而外聯(lián)接會(huì)返回 FROM 子句中提到的至少一個(gè)表或視圖的所有行，只要這些行符合任何 WHERE 或 HAVING 搜索條件。將檢索通過左向外聯(lián)接引用的左表的所有行，以及通過右向外聯(lián)接引用的右表的所有行。完整外部聯(lián)接中兩個(gè)表的所有行都將返回。Microsoft?SQL Se

9、rver? 2000 對(duì)在 FROM 子句中指定的外聯(lián)接使用以下SQL-92 關(guān)鍵字：·LEFT OUTER JOIN 或 LEFT JOIN·RIGHT OUTER JOIN 或 RIGHT JOIN·FULL OUTER JOIN 或 FULL JOINSQL Server 支持 SQL-92 外聯(lián)接語法，以及在 WHERE 子句中使用 *= 和 =* 運(yùn)算符指定外聯(lián)接的舊式語法。由于 SQL-92 語法不容易產(chǎn)生歧義，而舊式 Transact-SQL 外聯(lián)接有時(shí)會(huì)產(chǎn)生歧義，因此建議使用SQL-92 語法。使用左向外聯(lián)接假設(shè)在 city 列上聯(lián)接 author

10、s 表和 publishers 表。結(jié)果只顯示在出版商所在城市居住的作者（本例中為 Abraham Bennet和 Cheryl Carson）。若要在結(jié)果中包括所有的作者，而不管出版商是否住在同一個(gè)城市，請(qǐng)使用 SQL-92 左向外聯(lián)接。下面是 Transact-SQL 左向外聯(lián)接的查詢和結(jié)果： 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,

11、 a.au_fname ASC 下面是結(jié)果集：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 NUL

12、LMichael O'Leary 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 J

13、OIN 均會(huì)在結(jié)果中包含authors 表的所有行。注意：結(jié)果中所列的大多數(shù)作者都沒有相匹配的數(shù)據(jù) ,因此，這些行的 pub_name列包含空值。 使用右向外聯(lián)接假設(shè)在 city 列上聯(lián)接 authors 表和 publishers 表。結(jié)果只顯示在出版商所在城市居住的作者（本例中為 Abraham Bennet和 Cheryl Carson）。 SQL-92 右向外聯(lián)接運(yùn)算符 RIGHT OUTER JOIN 指明：不管第一個(gè)表中是否有匹配的數(shù)據(jù)，結(jié)果將包含第二個(gè)表中的所有行。若要在結(jié)果中包括所有的出版商，而不管城市中是否還有出版商居住，請(qǐng)使用 SQL-92 右向外聯(lián)接。下面是 Transa

14、ct-SQL 右向外聯(lián)接的查詢和結(jié)果： 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 下面是結(jié)果集：au_fname au_lname pub_name- - -Abraham Bennet AlgodataInfosystemsCheryl Carson AlgodataInfosystemsN

15、ULL 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)使用謂詞（如將聯(lián)接與常量比較）可以進(jìn)一步限制外聯(lián)接。下例包含相同的右向外聯(lián)接，但消除銷售量低于 50 本的書籍的書名：USE pubsSELECT s.stor_id, s.qty, t.titleFR

16、OM sales s RIGHT OUTER JOIN titles tON s.title_id = t.title_idAND s.qty> 50ORDER BY s.stor_id ASC下面是結(jié)果集：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 Balan

17、ce 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

18、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 Executive's Database Guide(null) (null) The Gourmet Microwave(null) (null) The Ps

19、ychology of Computer Cooking(null) (null) You Can Combat Computer Stress!(18 row(s) affected)有關(guān)謂詞的更多信息，請(qǐng)參見WHERE 。使用完整外部聯(lián)接若要通過在聯(lián)接結(jié)果中包括不匹配的行保留不匹配信息，請(qǐng)使用完整外部聯(lián)接。 Microsoft?SQL Server? 2000 提供完整外部聯(lián)接運(yùn)算符 FULL OUTER JOIN ，不管另一個(gè)表是否有匹配的值，此運(yùn)算符都包括兩個(gè)表中的所有行。假設(shè)在 city 列上聯(lián)接 authors 表和 publishers 表。結(jié)果只顯示在出版商所在城市居住的作者（

20、本例中為 Abraham Bennet和 Cheryl Carson）。 SQL-92 FULL OUTER JOIN 運(yùn)算符指明：不管表中是否有匹配的數(shù)據(jù)，結(jié)果將包括兩個(gè)表中的所有行。若要在結(jié)果中包括所有作者和出版商，而不管城市中是否有出版商或者出版商是否住在同一個(gè)城市，請(qǐng)使用完整外部聯(lián)接。下面是 Transact-SQL 完整外部聯(lián)接的查詢和結(jié)果：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 下面是結(jié)果集：au_fname au_lname pub_name- - -Reginald Blotchet-Halls NULLMic