MySQL JOIN | 連接

標籤：

連接是利用SQL的SELECT能執行的最重要的操作。為了提高儲存的有效性和避免資料冗餘，往往會將有關聯的資料存放區在好幾張表中，那麼怎樣用一條SELECT語句就能檢索出這些資料呢？

答案是JOIN（連接）。在一條SELECT語句中，我們可以連接多張表返回一組資料。
連接主要有4種，分別是INNER JOIN（內連接）、LEFT JOIN（左連接）、RIGHT JOIN（右連接）、CROSS JOIN（叉連接）

為了能清楚地說明這4種連接，首先需要準備好執行個體資料：

建立2個表：

CREATE TABLE product(id INT UNSIGNED NOT NULL AUTO_INCREMENT,name VARCHAR(30) NOT NULL,price INT UNSIGNED NOT NULL,vendor_id INT UNSIGNED NOT NULL,PRIMARY KEY (id));

CREATE TABLE vendor1(id INT UNSIGNED NOT NULL AUTO_INCREMENT,name VARCHAR(30) NOT NULL,PRIMARY KEY (id));

插入資料：

INSERT INTO product (name,price,vendor_id) VALUES(‘bread‘, 10, 1);INSERT INTO product (name,price,vendor_id) VALUES(‘beer‘, 27, 2);INSERT INTO product (name,price,vendor_id) VALUES(‘vegetables‘, 15, 0);INSERT INTO product (name,price,vendor_id) VALUES(‘beef‘, 22, 3);INSERT INTO product (name,price,vendor_id) VALUES(‘noodles‘, 30, 2);INSERT INTO product (name,price,vendor_id) VALUES(‘fish‘, 45, 0);INSERT INTO product (name,price,vendor_id) VALUES(‘milk‘, 22, 3);INSERT INTO product (name,price,vendor_id) VALUES(‘water‘, 18, 1);

INSERT INTO vendor (name) VALUES(‘hellen‘);INSERT INTO vendor (name) VALUES(‘ella‘);INSERT INTO vendor (name) VALUES(‘matt‘);INSERT INTO vendor (name) VALUES(‘jake‘);

 

說明：product表中儲存商品資訊，其中，有一部分商品是由店鋪老闆自己提供，所以這類商品沒有供應商，用vendor_id = 0表示

 

1. INNER JOIN | 內連接

樣本1-1：
找出product表中由ella提供的所有商品：

SELECT product.name, product.price, vendor.name FROM productINNER JOIN vendorON product.vendor_id = vendor.idWHERE vendor.name = ‘ella‘;

結果：

總結：

INNER JOIN可以檢索出符合條件(JOIN條件和WHERE條件等)的表product和表vendor的交集，如所示：

2. LEFT JOIN | 左連接

樣本2-1:
列出product表中所有product，並列出其供應商

SELECT product.name, product.price, vendor.name FROM productLEFT JOIN vendorON product.vendor_id = vendor.id;

結果：

總結：

LEFT JOIN 會檢索出主表的所有記錄，並且如果附表中的記錄符合條件會附加到主表的記錄中，主表與附表之間的關係如所示：

3. RIGHT JOIN | 右連接

樣本3-1:
列出由ella和jake所提供的所有products

SELECT vendor.name as vendor_name,product.name as product_name FROM productRIGHT JOIN vendorON product.vendor_id = vendor.idWHERE vendor.name IN (‘ella‘, ‘jake‘);

結果：

總結：
RIGHT JOIN 會檢索出附表的所有記錄，並且如果主表中的記錄符合條件會附加到附表的記錄中，主表與附表之間的關係如所示：

 

4. CROSS JOIN | 叉連接

樣本4-1:
列出由ella和hellen所提供的所有products

SELECT product.name as product_name, product.price, vendor.name as vendor_nameFROM product, vendorWHERE product.vendor_id = vendor.id AND vendor.name IN (‘ella‘, ‘hellen‘);

結果：

CROSS JOIN 返回主表中合格每一行與附表中合格每一行的配對。

如果沒有WHERE子句，可以更清楚看到CROSS JOIN的返回結果：

樣本4-2:

SELECT * FROM product,vendor;

 結果：

很明顯，返回的結果是主表中的每一行與附表中的每一行的配對。

MySQL JOIN | 連接