MySQL系列教程 --- 78、MySQL 中的 自然连接

当我们根据它们之间的公共列组合两个或多个表的行时，此操作称为连接。自然连接是一种连接操作，它通过基于具有相同名称和数据类型的列组合表来创建隐式连接。它类似于INNER或LEFT JOIN，但我们不能像在它们中使用的那样将 ON 或 USING 子句与自然连接一起使用。

要记住的要点：

• 无需指定要加入的列名。
• 结果表始终包含唯一的列。
• 可以对两个以上的表执行自然连接。
• 不要使用 ON 子句。

句法：

以下是说明自然连接的基本语法：

SELECT [column_names | *]   
FROM table_name1   
NATURAL JOIN table_name2;  

在此语法中，我们需要在 SELECT 关键字之后指定要包含在结果集中的列名。如果我们想从两个表中选择所有列，将使用***运算符。接下来，我们将在 FROM 关键字之后指定要加入的表名，并在它们之间写入NATURAL JOIN**子句。

自然连接示例

让我们通过示例了解自然连接在MySQL 中是如何工作的。首先，我们将使用以下语句创建两个名为customer和balance的表：

/* -- Table name: customer -*/  
CREATE TABLE customer (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    customer_name VARCHAR(55),  
    account int,  
    email VARCHAR(55)  
);    

/* -- Table name: balance -*/  
CREATE TABLE balance (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    account int,  
    balance FLOAT(10, 2)  
);  

接下来，我们将使用以下语句将一些记录填充到两个表中：

/* -- Data for customer table -*/  
INSERT INTO customer(customer_name, account, email)  
VALUES('Stephen', 1030, 'stephen@javatpoint.com'),  
    ('Jenifer', 2035, 'jenifer@javatpoint.com'),  
    ('Mathew', 5564, 'mathew@javatpoint.com'),  
    ('Smith', 4534, 'smith@javatpoint.com'),  
    ('David', 7648, 'david@javatpoint.com');  

/* -- Data for balance table -*/  
INSERT INTO balance(account, balance)  
VALUES(1030, 50000.00),   
    (2035, 230000.00),   
    (5564, 125000.00),   
    (4534, 80000.00),   
    (7648, 45000.00);  

接下来，我们将执行SELECT 语句来验证表数据：

现在，我们将看到满足自然连接标准的条件。我们可以通过使用DESCRIBE语句检查表结构来做到这一点。见下图：

在这张图片中，我们可以看到列名 id 和 account 及其数据类型都满足自然连接条件。因此我们可以对它们使用自然连接。

执行以下语句以使用自然连接连接表：

mysql> SELECT cust. customer_name, bal.balance   
FROM customer AS cust   
NATURAL JOIN balance AS bal;  

我们将得到以下结果：

我们可以在使用 ON 子句的INNER JOIN的帮助下完成相同的工作。这是解释此连接的查询：

mysql> SELECT cust. customer_name, bal.balance   
FROM customer AS cust   
INNER JOIN balance AS bal   
ON cust.id = bal.id;  

执行成功后，我们会得到和自然连接一样的结果：

现在，我们将使用 (*) 代替列名，如下所示：

mysql> SELECT * FROM customer NATURAL JOIN balance;  

假设我们使用星号（*）代替列名，那么自然连接会自动搜索相同的列名及其数据类型并在内部连接它们。此外，它不会在输出中显示重复的列。因此，在执行上述语句后，我们应该得到以下输出：

使用 WHERE 子句自然连接

在WHERE子句是用来从表中返回过滤后的结果。以下示例使用自然连接子句说明了这一点：

mysql> SELECT cust. customer_name, bal.balance   
FROM customer AS cust   
NATURAL JOIN balance AS bal  
WHERE bal.balance > 50000;  

我们将得到以下结果，其中显示帐户余额大于 50000 的客户信息。

使用三个表的自然连接

我们知道自然连接也可以对两个以上的表进行连接操作。为了理解这一点，我们将使用如下语法：

SELECT [column_names | *]   
FROM table_name1   
NATURAL JOIN table_name2  
NATURAL JOIN table_name3;  

让我们使用以下语句创建另一个名为cust_info的表：

CREATE TABLE cust_info (  
    account int,  
    mobile VARCHAR(15),  
    address VARCHAR(65)  
);  

然后，我们将记录填充到这个表中：

INSERT INTO cust_info(account, mobile, address)  
VALUES(1030, '598675498654', '777 Brockton Avenue, Abington MA 251'),   
    (2035, '698853747888', '337 Russell St, Hadley MA 103'),   
    (5564, '234456977555', '20 Soojian Dr, Leicester MA 154'),   
    (4534, '987656789666', '780 Lynnway, Lynn MA 19'),   
    (7648, '756489372222', '700 Oak Street, Brockton MA 23');  

我们可以使用 SELECT 语句验证数据。见下图：

要使用自然连接连接三个表，我们需要执行如下语句：

mysql> SELECT C.customer_name, C.account, B.balance, I.mobile   
FROM customer AS C  
NATURAL JOIN balance AS B  
NATURAL JOIN cust_info AS I;  

它将给出以下结果。在这里我们可以看到帐号出现在所有三列中，但在满足自然连接条件的输出中只出现一次。

自然连接和内部连接的区别