,ON或USING條件的時(shí)候一般不建議使用，因?yàn)楫?dāng)數(shù)據(jù)表項(xiàng)目太多<" />

日韩久久久精品,亚洲精品久久久久久久久久久,亚洲欧美一区二区三区国产精品 ,一区二区福利

MySQL中的各種JOIN(CROSS JOIN, INNER JOIN, LE

系統(tǒng) 3255 0
MySQL中的各種JOIN

1. 笛卡爾積(交叉連接)
在MySQL中可以為CROSS JOIN或者省略CROSS即JOIN，或者使用','
如
SELECT * FROM table1 CROSS JOIN table2
SELECT * FROM table1 JOIN table2
SELECT * FROM table1,table2

由于其返回的結(jié)果為被連接的兩個(gè)數(shù)據(jù)表的乘積，因此當(dāng)有WHERE<wbr></wbr>, ON或USING條件的時(shí)候一般不建議使用，因?yàn)楫?dāng)數(shù)據(jù)表項(xiàng)目太多<wbr></wbr>的時(shí)候，會(huì)非常慢。
一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN

2. 內(nèi)連接INNER JOIN
在MySQL中把INNER JOIN叫做等值連接，即需要指定等值連接條件
在MySQL中CROSS和INNER JOIN被劃分在一起，不明白。
參看MySQL幫助手冊(cè)
http://dev.mysql.com/doc<wbr></wbr>/refman/5.0/en/join.html
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. MySQL中的外連接，分為左外連接和右連接，
即除了返回符合連接條件的結(jié)果之外，還要返回左表(左連接<wbr></wbr>)或者右表(右連接)中不符合連接條件的結(jié)果，相對(duì)應(yīng)的使用NUL<wbr></wbr>L對(duì)應(yīng)。

a. LEFT [OUTER] JOIN
SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column
除了返回符合連接條件的結(jié)果之外，還需要顯示左表中不符合連接條件<wbr></wbr>的數(shù)據(jù)列，相對(duì)應(yīng)使用NULL對(duì)應(yīng)

b. RIGHT [OUTER] JOIN
SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column
RIGHT與LEFT JOIN相似不同的僅僅是除了顯示符合連接條件的結(jié)果之外<wbr></wbr>，還需要顯示右表中不符合連接條件的數(shù)據(jù)列，相應(yīng)使用NULL對(duì)應(yīng)


------------------------------<wbr></wbr>--------------
添加顯示條件WHERE, ON, USING
1. WHERE子句
2. ON
3. USING子句，如果連接的兩個(gè)表連接條件的兩個(gè)列具有相同的名字<wbr></wbr>的話可以使用USING
例如
SELECT <column_name> FROM <table1> LEFT JOIN <table2> USING (<column_name>)

連接多余兩個(gè)表的情況
舉例：
mysql> SELECT artists.Artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID
-> LEFT JOIN artists
-> ON cds.artistID = artists.artistID;
或者
mysql> SELECT artists.Artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID
-> LEFT JOIN artists
-> ON cds.artistID = artists.artistID
-> WHERE (genres.genre = 'Pop');
------------------------------<wbr></wbr>--------------

另外需要注意的地方

在MySQL中涉及到多表查詢的時(shí)候，需要根據(jù)查詢的情況<wbr></wbr>，想好使用哪種連接方式效率更高。
1. 交叉連接(笛卡爾積)或者內(nèi)連接
[INNER | CROSS] JOIN
2. 左外連接LEFT [OUTER] JOIN或者右外連接RIGHT [OUTER] JOIN

注意指定連接條件WHERE, ON，USING.

------------------------------<wbr></wbr>--------------
看懂MySQL手冊(cè)定義的MySQL各種JOIN的用法：
//看懂如下的定義方式 
                
                  
                    table_references:
                  
                
                

                
                  
                    table_reference
                  
                
                 [, 
                
                  

                  
                    table_reference
                  
                
                ] ...
                

                

                
                  //不同的JOIN EXPRESSION之間使用','分割
                
                

                A table reference is also known as a join expression.
                

                

                
                  
                    

                    table_reference
                  
                
                :
                

                
                  
                    table_factor
                  
                
                

                  | 
                
                  
                    join_table
                    

                    

                  
                
                
                  

                  //每個(gè)JOIN EXPRESSION由數(shù)據(jù)表table_factor以及JOI<wbr></wbr>N表達(dá)式構(gòu)成join_table
                
                

                
                  
                
                

                
                  
                    table_factor
                  
                
                :
                

                
                  
                    

                    tbl_name
                  
                
                 [[AS] 
                
                  
                    alias
                  
                
                ] [
                
                  
                    index_hint
                  
                
                )]
                

                  | ( 
                
                  
                    table_references
                  
                
                 )
                

                  | { OJ 
                
                  

                  
                    table_reference
                  
                
                 LEFT OUTER JOIN 
                
                  
                    table_reference
                  
                
                

                        ON 
                
                  
                    conditional_expr
                  
                
                 }
                

                
                  

                  

                  
                    //
                  
                  數(shù)據(jù)表table_factor，注意其遞歸定義的table<wbr></wbr>_references
                
                

                

                

                
                  
                    join_table
                  
                
                :
                

                
                  
                    table_reference
                  
                
                 [INNER | CROSS] JOIN 
                

                
                  
                    table_factor
                  
                
                 [
                
                  
                    join_condition
                  
                
                ]
                

                  | 
                
                  
                    table_reference
                  
                
                 STRAIGHT_JOIN 
                
                  
                    

                    table_factor
                  
                
                

                  | 
                
                  
                    table_reference
                  
                
                 STRAIGHT_JOIN 
                
                  
                    table_factor
                  
                
                 ON 
                
                  
                    condition
                  
                
                

                  | 
                

                
                  
                    table_reference
                  
                
                 LEFT [OUTER] JOIN 
                
                  
                    table_reference
                  
                
                
                  
                    join_condition
                  
                
                

                  | 
                
                  

                  
                    table_reference
                  
                
                 NATURAL [LEFT [OUTER]] JOIN 
                
                  
                    table_factor
                  
                
                

                  | 
                
                  
                    table_reference
                  
                
                 RIGHT [OUTER] JOIN 
                
                  

                  
                    table_reference
                  
                
                
                  
                    join_condition
                  
                
                

                  | 
                
                  
                    table_reference
                  
                
                 NATURAL [RIGHT [OUTER]] JOIN 
                
                  
                    table_factor
                    

                  
                
                

                

                
                  //數(shù)據(jù)表的連接表達(dá)式j(luò)oin_table
                
                

                

                
                  
                    join_condition
                  
                
                :
                

                    ON 
                
                  
                    conditional_expr
                  
                
                

                

                  | USING (
                
                  
                    column_list
                  
                
                )
                

                
                  

                  //連接表達(dá)式的連接條件定義使用ON或者USING
                
                

                

                
                  
                    index_hint
                  
                
                :
                

                    USE {INDEX|KEY} [FOR JOIN] (
                
                  
                    index_list
                  
                  

                
                )
                

                  | IGNORE {INDEX|KEY} [FOR JOIN] (
                
                  
                    index_list
                  
                
                )
                

                  | FORCE {INDEX|KEY} [FOR JOIN] (
                
                  
                    index_list
                  
                
                )
                

                

                
                  

                  
                    index_list
                  
                
                :
                

                
                  
                    index_name
                  
                
                 [, 
                
                  
                    index_name
                  
                
                ] ...

MySQL手冊(cè)中提到的JOIN需要注意的地方：

1.
In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
手冊(cè)中提到
標(biāo)準(zhǔn)SQL中CROSS JOIN交叉連接(笛卡爾積)和內(nèi)連接INNER JOIN不同，但是MySQL中兩者是相同的，即有[CROSS | INNER] JOIN，兩者可以互相替代，而且可以只使用JOIN

2. A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name : 
                SELECT 
                
                  t1.name
                
                , t2.salary
                

                  FROM employee AS t1 INNER JOIN info AS t2 ON 
                
                  t1.name
                
                 = 
                
                  t2.name
                
                ;
                
                  

                  可以對(duì)數(shù)據(jù)表使用別名
                  

                  

                  3. ,運(yùn)算符
                  

                  例如
                  

                  SELECT * FROM table1,table2 
                  

                  由于在MySQL中INNER JOIN與CROSS JOIN相同，INNER JOIN和 , 在MySQL也相同，都是產(chǎn)生兩個(gè)表的笛卡爾積Cartesian Product
                  

                  (等于兩個(gè)表格的行數(shù)乘積)
                  

                  

                  但是,號(hào)的優(yōu)先級(jí)要低于INNER JOIN, CROSS JOIN, LEFT JOIN
                  

                  

                  因此
                  

                  If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.
                  

                  

                  4. 什么時(shí)候使用ON，什么時(shí)候使用WHERE
                  

                  ON應(yīng)該用戶數(shù)據(jù)表連接的時(shí)候指定連接條件；
                  

                  

                  WHERE用于用戶限制所選取的列
                  

                  

                  例如ON a.column=b.column
                  

                  WHERE a.column='hello'
                  

                  

                  5. 可以使用LEFT JOIN查看，兩個(gè)連接的表中，不符合連接條件的部分<wbr></wbr>，因?yàn)椴环蠗l件的部分LEFT JOIN之后會(huì)顯示為NULL 
                  

                   If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:
                  

                  

                  

                  SELECT left_tbl.*
                  

                    FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
                  

                    WHERE right_tbl.id IS NULL;
                  

                  

                  This example finds all rows in left_tbl with an id value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl). This assumes that right_tbl.id is declared NOT NULL. 
                  

                  

                  

                  6.
                  

                  當(dāng)別連接的表指定連接條件的列舉有相同的名稱的時(shí)候，不需要
                  

                  ON a.column=b.column不同的時(shí)候才使用ON a.column_a=b.column_b
                  

                  可以使用USING (column)
                  

                  當(dāng)然也可以使用多個(gè)USING (c1,c2,c3)
                  

                  

                  The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:
                  

                  

                  

                  a LEFT JOIN b USING (c1,c2,c3)
                  

                  

                  7.
                  

                  其他的：
                  

                  #
                  

                  The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.
                  

                  

                  #
                  

                  RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.
                  

                  #
                  

                  The { OJ ... LEFT OUTER JOIN ...} syntax shown in the join syntax description exists only for compatibility with ODBC. The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions.
                  

                  

                  #
                  

                  STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.
參考資料
http://www.w3schools.com/sql<wbr></wbr>/sql_join.asp
http://www.keithjbrown.co.uk<wbr></wbr>/vworks/mysql/mysql_p5.php
http://dev.mysql.com/doc<wbr></wbr>/refman/5.0/en/join.html

回復(fù) 轉(zhuǎn)發(fā)

MySQL中的各種JOIN(CROSS JOIN, INNER JOIN, LEFT [OUTER] JOIN)


更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號(hào)聯(lián)系： 360901061

您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對(duì)您有幫助就好】元

您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧，站長會(huì)非常 感謝您的哦！！！

發(fā)表我的評(píng)論
最新評(píng)論 總共0條評(píng)論
主站蜘蛛池模板： 桃园县| 哈巴河县| 广平县| 云林县| 保定市| 昌平区| 深圳市| 青冈县| 临城县| 南漳县| 赤城县| 德江县| 从江县| 桂林市| 新密市| 大洼县| SHOW| 驻马店市| 钟祥市| 荃湾区| 铜山县| 军事| 苍山县| 大同县| 惠安县| 丁青县| 霸州市| 宣汉县| 慈溪市| 邛崃市| 印江| 盖州市| 绥棱县| 灵武市| 古田县| 林甸县| 穆棱市| 乡城县| 鹿邑县| 西乌珠穆沁旗| 阿坝县|