您的位置:首頁 > 教程 > SQL server > SQL中的連接查詢詳解

SQL中的連接查詢詳解

2022-06-18 11:47:39 來源:易采站長站 作者:

SQL中的連接查詢詳解

Join 連接 (SQL>

SQL Join (連接) 是利用不同數據表之間字段的關連性來結合多數據表之檢索。xB7站長之家-易采站長站-Easck.Com

SQL Join是結合多個數據表而組成一抽象的暫時性數據表以供數據查詢,在原各數據表中之紀錄及結構皆不會因此連接查詢而改變。xB7站長之家-易采站長站-Easck.Com

這是一個客戶數據表「customers」:xB7站長之家-易采站長站-Easck.Com

C_IdxB7站長之家-易采站長站-Easck.Com

NamexB7站長之家-易采站長站-Easck.Com

CityxB7站長之家-易采站長站-Easck.Com

AddressxB7站長之家-易采站長站-Easck.Com

PhonexB7站長之家-易采站長站-Easck.Com

1xB7站長之家-易采站長站-Easck.Com

張一xB7站長之家-易采站長站-Easck.Com

臺北市xB7站長之家-易采站長站-Easck.Com

XX路100號xB7站長之家-易采站長站-Easck.Com

02-12345678xB7站長之家-易采站長站-Easck.Com

2xB7站長之家-易采站長站-Easck.Com

王二xB7站長之家-易采站長站-Easck.Com

新竹縣xB7站長之家-易采站長站-Easck.Com

YY路200號xB7站長之家-易采站長站-Easck.Com

03-12345678xB7站長之家-易采站長站-Easck.Com

3xB7站長之家-易采站長站-Easck.Com

李三xB7站長之家-易采站長站-Easck.Com

高雄縣xB7站長之家-易采站長站-Easck.Com

ZZ路300號xB7站長之家-易采站長站-Easck.Com

07-12345678xB7站長之家-易采站長站-Easck.Com

而這是產品訂單的數據表「orders」:xB7站長之家-易采站長站-Easck.Com

O_IdxB7站長之家-易采站長站-Easck.Com

OrderNoxB7站長之家-易采站長站-Easck.Com

C_IdxB7站長之家-易采站長站-Easck.Com

1xB7站長之家-易采站長站-Easck.Com

2572xB7站長之家-易采站長站-Easck.Com

3xB7站長之家-易采站長站-Easck.Com

2xB7站長之家-易采站長站-Easck.Com

7375xB7站長之家-易采站長站-Easck.Com

3xB7站長之家-易采站長站-Easck.Com

3xB7站長之家-易采站長站-Easck.Com

7520xB7站長之家-易采站長站-Easck.Com

1xB7站長之家-易采站長站-Easck.Com

4xB7站長之家-易采站長站-Easck.Com

1054xB7站長之家-易采站長站-Easck.Com

1xB7站長之家-易采站長站-Easck.Com

5xB7站長之家-易采站長站-Easck.Com

1257xB7站長之家-易采站長站-Easck.Com

5xB7站長之家-易采站長站-Easck.Com

其中,C_Id 是客戶數據表中的主鍵 (Primary Key) 字段,我們怎么將這兩張不同的數據表依相關字段來作個連接結合以便查詢呢?這就是接下來的主題 Join!xB7站長之家-易采站長站-Easck.Com

SQL 的 Join 查詢有哪幾種類型?

    Inner>

    LEFT (OUTER) JOIN : 左外部連接xB7站長之家-易采站長站-Easck.Com

    RIGHT (OUTER) JOIN : 右外部連接xB7站長之家-易采站長站-Easck.Com

    FULL (OUTER) JOIN : 全部外部連接xB7站長之家-易采站長站-Easck.Com

    CROSS JOIN : 交叉連接xB7站長之家-易采站長站-Easck.Com

    NATURAL JOIN : 自然連接xB7站長之家-易采站長站-Easck.Com

    INNER>

    INNER JOIN (內部連接) 為等值連接,必需指定等值連接的條件,而查詢結果只會返回符合連接條件的數據。xB7站長之家-易采站長站-Easck.Com

    INNER>
    SELECT table_column1, table_column2···
    FROM table_name1
    INNER JOIN table_name2
    ON table_name1.column_name=table_name2.column_name;

    xB7站長之家-易采站長站-Easck.Com

    SELECT table_column1, table_column2···
    FROM table_name1
    INNER JOIN table_name2
    USING (column_name);

    INNER>

    現在我們想列出所有客戶的訂單編號數據,我們可以作一個 INNER JOIN 查詢:xB7站長之家-易采站長站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    INNER JOIN orders
    ON customers.C_Id=orders.C_Id;

    其中用點號連接之「XXX.YYY」表示XXX數據表中的YYY字段。xB7站長之家-易采站長站-Easck.Com

    查詢結果如下:xB7站長之家-易采站長站-Easck.Com

    NamexB7站長之家-易采站長站-Easck.Com

    Order_NoxB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    2572xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    7375xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    7520xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    1054xB7站長之家-易采站長站-Easck.Com

    查詢結果只會返回符合連接條件的數據!xB7站長之家-易采站長站-Easck.Com

    LEFT>

    LEFT JOIN 可以用來建立左外部連接,查詢的 SQL 敘述句 LEFT JOIN 左側數據表 (table_name1) 的所有記錄都會加入到查詢結果中,即使右側數據表 (table_name2) 中的連接字段沒有符合的值也一樣。xB7站長之家-易采站長站-Easck.Com

    LEFT>
    SELECT table_column1, table_column2···
    FROM table_name1
    LEFT JOIN table_name2
    ON table_name1.column_name=table_name2.column_name;

    有些數據庫的語法會是LEFT OUTER JOIN。xB7站長之家-易采站長站-Easck.Com

    LEFT>

    現在我們想查詢所有客戶與其訂單狀況的數據,我們可以作一個 LEFT JOIN 查詢:xB7站長之家-易采站長站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    LEFT JOIN orders
    ON customers.C_Id=orders.C_Id;

    查詢結果如下:xB7站長之家-易采站長站-Easck.Com

    NamexB7站長之家-易采站長站-Easck.Com

    Order_NoxB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    7520xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    1054xB7站長之家-易采站長站-Easck.Com

    王二xB7站長之家-易采站長站-Easck.Com

     

    李三xB7站長之家-易采站長站-Easck.Com

    2572xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    7375xB7站長之家-易采站長站-Easck.Com

    LEFT JOIN會返回左側數據表中所有數據列,就算沒有符合連接條件,而右側數據表中如果沒有匹配的數據值就會顯示為「NULL」。xB7站長之家-易采站長站-Easck.Com

    RIGHT>

    相對于LEFT JOIN,RIGHT JOIN 可以用來建立右外部連接,查詢的 SQL 敘述句 RIGHT JOIN 右側數據表 (table_name2) 的所有記錄都會加入到查詢結果中,即使左側數據表 (table_name2) 中的連接字段沒有符合的值也一樣。xB7站長之家-易采站長站-Easck.Com

    RIGHT>
    SELECT table_column1, table_column2···
    FROM table_name1
    RIGHT JOIN table_name2
    ON table_name1.column_name=table_name2.column_name;

    有些數據庫的語法會是RIGHT OUTER JOIN。xB7站長之家-易采站長站-Easck.Com

    RIGHT>

    現在我們想查詢所有訂單與相應的客戶之資料,我們可以作一個 RIGHT JOIN 查詢:xB7站長之家-易采站長站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    RIGHT JOIN orders
    ON customers.C_Id=orders.C_Id;

    查詢結果如下:xB7站長之家-易采站長站-Easck.Com

    NamexB7站長之家-易采站長站-Easck.Com

    Order_NoxB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    2572xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    7375xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    7520xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    1054xB7站長之家-易采站長站-Easck.Com

     

    1257xB7站長之家-易采站長站-Easck.Com

    RIGHT JOIN會返回右側數據表中所有數據列,就算是沒有符合連接條件,而左側數據表中如果沒有匹配的數據值就會顯示為「NULL」。xB7站長之家-易采站長站-Easck.Com

    FULL>

    FULL JOIN 即為 LEFT JOIN 與 RIGHT JOIN 的聯集,它會返回左右數據表中所有的紀錄,不論是否符合連接條件。xB7站長之家-易采站長站-Easck.Com

    FULL>
    SELECT table_column1, table_column2···
    FROM table_name1
    FULL JOIN table_name2
    ON table_name1.column_name=table_name2.column_name;

    FULL>

    我們來作一個 FULL JOIN 查詢:xB7站長之家-易采站長站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    FULL JOIN orders
    ON customers.C_Id=orders.C_Id;

    查詢結果如下:xB7站長之家-易采站長站-Easck.Com

    NamexB7站長之家-易采站長站-Easck.Com

    Order_NoxB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    2572xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    7375xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    7520xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    1054xB7站長之家-易采站長站-Easck.Com

     

    1257xB7站長之家-易采站長站-Easck.Com

    王二xB7站長之家-易采站長站-Easck.Com

     

    MySQL數據庫中沒有FULL JOIN,但是您可以用UNION來模擬。xB7站長之家-易采站長站-Easck.Com

    CROSS>

    交叉連接為兩個數據表間的笛卡兒乘積 (Cartesian product),兩個數據表在結合時,不指定任何條件,即將兩個數據表中所有的可能排列組合出來,以下例而言 CROSS JOIN 出來的結果資料列數為 3×5=15 筆,因此,當有WHERE、ON、USING條件時不建議使用。xB7站長之家-易采站長站-Easck.Com

    CROSS>
    SELECT table_column1, table_column2···
    FROM table_name1
    CROSS JOIN table_name2;

    xB7站長之家-易采站長站-Easck.Com

    SELECT table_column1, table_column2···
    FROM table_name1, table_name2;

    xB7站長之家-易采站長站-Easck.Com

    SELECT table_column1, table_column2···
    FROM table_name1
    JOIN table_name2;

    FULL>

    這是一個客戶數據表「customers」:xB7站長之家-易采站長站-Easck.Com

    C_IdxB7站長之家-易采站長站-Easck.Com

    NamexB7站長之家-易采站長站-Easck.Com

    CityxB7站長之家-易采站長站-Easck.Com

    AddressxB7站長之家-易采站長站-Easck.Com

    PhonexB7站長之家-易采站長站-Easck.Com

    1xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    臺北市xB7站長之家-易采站長站-Easck.Com

    XX路100號xB7站長之家-易采站長站-Easck.Com

    02-12345678xB7站長之家-易采站長站-Easck.Com

    2xB7站長之家-易采站長站-Easck.Com

    王二xB7站長之家-易采站長站-Easck.Com

    新竹縣xB7站長之家-易采站長站-Easck.Com

    YY路200號xB7站長之家-易采站長站-Easck.Com

    03-12345678xB7站長之家-易采站長站-Easck.Com

    3xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    高雄縣xB7站長之家-易采站長站-Easck.Com

    ZZ路300號xB7站長之家-易采站長站-Easck.Com

    07-12345678xB7站長之家-易采站長站-Easck.Com

    而這是產品訂單的數據表「orders」:xB7站長之家-易采站長站-Easck.Com

    O_IdxB7站長之家-易采站長站-Easck.Com

    Order_NoxB7站長之家-易采站長站-Easck.Com

    C_IdxB7站長之家-易采站長站-Easck.Com

    1xB7站長之家-易采站長站-Easck.Com

    2572xB7站長之家-易采站長站-Easck.Com

    3xB7站長之家-易采站長站-Easck.Com

    2xB7站長之家-易采站長站-Easck.Com

    7375xB7站長之家-易采站長站-Easck.Com

    3xB7站長之家-易采站長站-Easck.Com

    3xB7站長之家-易采站長站-Easck.Com

    7520xB7站長之家-易采站長站-Easck.Com

    1xB7站長之家-易采站長站-Easck.Com

    4xB7站長之家-易采站長站-Easck.Com

    1054xB7站長之家-易采站長站-Easck.Com

    1xB7站長之家-易采站長站-Easck.Com

    5xB7站長之家-易采站長站-Easck.Com

    1257xB7站長之家-易采站長站-Easck.Com

    5xB7站長之家-易采站長站-Easck.Com

    我們來作一個 CROSS JOIN 查詢:xB7站長之家-易采站長站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    CROSS JOIN orders;

    查詢結果如下:xB7站長之家-易采站長站-Easck.Com

    NamexB7站長之家-易采站長站-Easck.Com

    Order_NoxB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    2572xB7站長之家-易采站長站-Easck.Com

    王二xB7站長之家-易采站長站-Easck.Com

    2572xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    2572xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    7375xB7站長之家-易采站長站-Easck.Com

    王二xB7站長之家-易采站長站-Easck.Com

    7375xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    7375xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    7520xB7站長之家-易采站長站-Easck.Com

    王二xB7站長之家-易采站長站-Easck.Com

    7520xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    7520xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    1054xB7站長之家-易采站長站-Easck.Com

    王二xB7站長之家-易采站長站-Easck.Com

    1054xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    1054xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    1257xB7站長之家-易采站長站-Easck.Com

    王二xB7站長之家-易采站長站-Easck.Com

    1257xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    1257xB7站長之家-易采站長站-Easck.Com

    NATURAL>

    自然連接有 NATURAL JOIN、NATURAL LEFT JOIN、NATURAL RIGHT JOIN,兩個表格在進行 JOIN 時,加上 NATURAL 這個關鍵詞之后,兩數據表之間同名的字段會被自動結合在一起。xB7站長之家-易采站長站-Easck.Com

    NATURAL>
    SELECT table_column1, table_column2···
    FROM table_name1
    NATURAL JOIN table_name2;

    NATURAL>

    這是一個客戶數據表「customers」:xB7站長之家-易采站長站-Easck.Com

    C_IdxB7站長之家-易采站長站-Easck.Com

    NamexB7站長之家-易采站長站-Easck.Com

    CityxB7站長之家-易采站長站-Easck.Com

    AddressxB7站長之家-易采站長站-Easck.Com

    PhonexB7站長之家-易采站長站-Easck.Com

    1xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    臺北市xB7站長之家-易采站長站-Easck.Com

    XX路100號xB7站長之家-易采站長站-Easck.Com

    02-12345678xB7站長之家-易采站長站-Easck.Com

    2xB7站長之家-易采站長站-Easck.Com

    王二xB7站長之家-易采站長站-Easck.Com

    新竹縣xB7站長之家-易采站長站-Easck.Com

    YY路200號xB7站長之家-易采站長站-Easck.Com

    03-12345678xB7站長之家-易采站長站-Easck.Com

    3xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    高雄縣xB7站長之家-易采站長站-Easck.Com

    ZZ路300號xB7站長之家-易采站長站-Easck.Com

    07-12345678xB7站長之家-易采站長站-Easck.Com

    而這是產品訂單的數據表「orders」:xB7站長之家-易采站長站-Easck.Com

    O_IdxB7站長之家-易采站長站-Easck.Com

    Order_NoxB7站長之家-易采站長站-Easck.Com

    C_IdxB7站長之家-易采站長站-Easck.Com

    1xB7站長之家-易采站長站-Easck.Com

    2572xB7站長之家-易采站長站-Easck.Com

    3xB7站長之家-易采站長站-Easck.Com

    2xB7站長之家-易采站長站-Easck.Com

    7375xB7站長之家-易采站長站-Easck.Com

    3xB7站長之家-易采站長站-Easck.Com

    3xB7站長之家-易采站長站-Easck.Com

    7520xB7站長之家-易采站長站-Easck.Com

    1xB7站長之家-易采站長站-Easck.Com

    4xB7站長之家-易采站長站-Easck.Com

    1054xB7站長之家-易采站長站-Easck.Com

    1xB7站長之家-易采站長站-Easck.Com

    5xB7站長之家-易采站長站-Easck.Com

    1257xB7站長之家-易采站長站-Easck.Com

    5xB7站長之家-易采站長站-Easck.Com

    現在我們想列出所有客戶的訂單編號數據,我們可以作一個 NATURAL JOIN 查詢:xB7站長之家-易采站長站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    NATURAL JOIN orders;

    查詢結果如下:xB7站長之家-易采站長站-Easck.Com

    NamexB7站長之家-易采站長站-Easck.Com

    Order_NoxB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    2572xB7站長之家-易采站長站-Easck.Com

    李三xB7站長之家-易采站長站-Easck.Com

    7375xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    7520xB7站長之家-易采站長站-Easck.Com

    張一xB7站長之家-易采站長站-Easck.Com

    1054xB7站長之家-易采站長站-Easck.Com

    注意到了嗎?返回結果同等于下面這個INNER JOIN查詢:xB7站長之家-易采站長站-Easck.Com

    SELECT customers.Name, orders.Order_No
    FROM customers
    INNER JOIN orders
    ON customers.C_Id=orders.C_Id;

    到此這篇關于SQL連接查詢的文章就介紹到這了。希望對大家的學習有所幫助,也希望大家多多支持易采站長站。xB7站長之家-易采站長站-Easck.Com

    如有侵權,請聯系QQ:279390809 電話:15144810328

相關文章

  • SQL Server 2019下載與安裝教程(自定義安裝)

    SQL Server 2019下載與安裝教程(自定義安裝)

    1.SQL Server2019安裝包下載 1.1進入官網 SQL Server 2019 1.2下載安裝包 1點擊Continue 2.填寫個人信息,再點擊Continue 3.點擊保存文件,就代表開始下載安裝包 4.下載完成后,找到安裝包并以管理員
    2020-04-28
  • 詳解SQL Server表和索引存儲結構

    詳解SQL Server表和索引存儲結構

    本文詳細分析了SQL Server中表和索引結構存儲的原理以及對于如何加快搜索速度和提高效率等方面做了詳細的分析,以下是主要內容。 下圖顯示了表的存儲組織,每張表有一個對應的對
    2020-07-04
  • SQL UNION 操作符

    SQL UNION 操作符

    SQL UNION 操作符 SQL UNION 操作符合并兩個或多個 SELECT 語句的結果。 SQL UNION 操作符 UNION 操作符用于合并兩個或多個 SELECT 語句的結果集。 請注意,UNION 內部的每個 SELECT 語句必須擁有相同
    2020-07-04
  • SQL Server的IP不能連接問題解決

    SQL Server的IP不能連接問題解決

    自己電腦上安裝的SQLServer,用了一段時間后要聯網發現連接不上,花了兩三個小時才搞定,在此記錄下免得再遇上 剛開始時用navicat連接 使用IP連接: 要是用IP連接的需要幾個步驟:
    2020-07-04
  • SQL SERVER 分組求和sql語句

    SQL SERVER 分組求和sql語句

    需求:如下圖所示 實現sql語句 SELECT A1,SUM(A2*A3) FROM A GROUP BY A1 大家可以自行測試一下,主要需要了解group by語句的用法 您可能感興趣的文章: 分組后分組合計以及總計SQL語句(稍微整理
    2020-07-04
  • SQL Server 2012 sa用戶登錄錯誤18456的解決方法

    SQL Server 2012 sa用戶登錄錯誤18456的解決方法

    最近想研究下SQL SERVER2012 Enterprise版本的數據庫,聽說功能很強大。我是在win7上安裝的,安裝的過程很順利,我在用“Windows 身份驗證”時,一切OK,但是在用SA用戶登錄數據庫的時候出
    2020-07-04
  • SQL Server中T-SQL 數據類型轉換詳解

    SQL Server中T-SQL 數據類型轉換詳解

    常用的轉換函數是 cast 和 convert,用于把表達式得出的值的類型轉換成另一個數據類型,如果轉換失敗,該函數拋出錯誤,導致整個事務回滾。在SQL Server 2012版本中,新增兩個容錯的轉
    2020-07-04
  • SQL Server數據庫設置自動備份策略的完整步驟

    SQL Server數據庫設置自動備份策略的完整步驟

    先了解一下:為何要做備份? 數據備份是容災的基礎,是指為防止系統出現操作失誤或系統故障導致數據丟失,而將全部或部分數據集合從應用主機的硬盤或陣列復制到其它的存儲介質
    2020-07-04
色七七影院_香港三级台湾三级在线播放_男人放进女人阳道猛进猛出