MySQL JOIN | 联结

联结是利用SQL的SELECT能执行的最重要的操作。为了提高存储的有效性和避免数据冗余，往往会将有关联的数据存储在好几张表中，那么怎样用一条SELECT语句就能检索出这些数据呢？

答案是JOIN（联结）。在一条SELECT语句中，我们可以联结多张表返回一组数据。

联结的本质是主表中符合条件的每一行与附表中符合条件的每一行进行配对，假如没有WHERE子句或联结条件，则主表中每一行将与附表中的每一行配对，总共会返回的行数是main_table_row_num * attach_table_row_num。如果主表和附表中包含的行数非常多，那么就有必要在WHERE子句中加入筛选条件，这样会大大减少不必要的配对。

联结主要有4种，分别是INNER JOIN（内联结）、LEFT JOIN（左联结）、RIGHT JOIN（右联结）、CROSS JOIN（叉联结）。

 

为了能清楚地说明这4种联结，我准备了如下示例数据：

一个product表，用来保存商品的名称、价格和供应商ID，一个vendor表，用来保存供应商ID，供应商名称。product表和vendor表之间通过product.vendor_id = vendor.id进行关联。

创建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 product
INNER JOIN vendor
ON product.vendor_id = vendor.id
WHERE vendor.name = 'ella';

结果：

总结：

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

2. LEFT JOIN | 左联结

示例2-1:
列出product表中所有product，并列出其供应商

SELECT product.name, product.price, vendor.name FROM product
LEFT JOIN vendor
ON 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 product
RIGHT JOIN vendor
ON product.vendor_id = vendor.id
WHERE 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_name
FROM product, vendor
WHERE product.vendor_id = vendor.id AND vendor.name IN ('ella', 'hellen');

结果：

CROSS JOIN 返回主表中符合条件的每一行与附表中符合WHERE条件的每一行的配对。

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

示例4-2:

SELECT * FROM product,vendor;

 结果：

很明显，返回的结果是主表中的每一行与附表中的每一行的配对。

如果您觉得阅读本文对您有帮助，欢迎转载本文，但是转载文章之后必须在文章页面明显位置保留此段声明，否则保留追究法律责任的权利。

作　　者：www.jpdou.top

原文链接：MySQL JOIN | 联结