type
Post
status
Published
slug
sql/1
summary
SQL JOIN 子句允许我们同时访问两个或多个表中的信息。 他们还使我们的数据库保持规范化。 规范化使我们能够保持较低的数据冗余，这样我们就可以在删除或更新记录时减少应用程序中的数据异常量。
tags
SQL
category
译文
password
结构化查询语言 (SQL) 允许我们对关系数据库中的单个表执行某种操作。 这些操作可以更新、创建、删除或选择该表中的记录。
如果我们有两个表包含关于同一个人的不同信息，并且我们想使用所有这些信息在那个人的发票上显示，怎么办？ 为此我们需要使用 JOIN 子句。
在本教程中，我们将定义什么是 JOIN 子句，讨论 JOIN 子句的类型，并为每种类型提供连接示例。
什么是 SQL JOIN？
SQL JOIN 子句允许我们同时访问两个或多个表中的信息。 他们还使我们的数据库保持规范化。 规范化使我们能够保持较低的数据冗余，这样我们就可以在删除或更新记录时减少应用程序中的数据异常量。
简单说：JOIN 子句允许我们根据相关列组合来自两个或多个表的行。
让我们用一个例子来说明我们的客户和客户的订单。 如果我们有一个包含 Customers 信息的 Customers 表和一个单独的 Orders 表：
在这些表中，请注意两个表中有很多相同的信息。 连接语句大大减少了对这些重复值的需求。 我们的新表可能如下所示：
我们可以通过使用 JOIN 子句查询数据库，从 Customers 表和 Orders 表中选择信息，以在我们的应用程序中需要的地方使用。
根据您的需要，有几种不同类型的连接语句。 在下一节中，我们将查看每种类型的示例。
JOIN 语句的类型
您使用的连接语句类型取决于您使用的用例。 有四种不同类型的 JOIN 操作用例：
(INNER) JOIN：返回在两个表中都具有匹配值的数据集
LEFT (OUTER) JOIN：返回左表中的所有记录和右表中匹配的记录
RIGHT (OUTER) JOIN：返回右表中的所有记录和左表中匹配的记录
FULL (OUTER) JOIN：当左表或右表中存在匹配项时返回所有记录
INNER JOIN（内连接）
如果您将每个表视为上面维恩图中的一个单独圆圈，则内连接将是两个圆圈相交的阴影区域。
只要连接条件满足，内连接就会从表中选择所有行。 此关键字将创建一个结果集，该结果集由存在公共字段的两个表中的组合行组成。
下面是内连接的语法：
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
下面是一个例子，结果集将省略任何具有 NULL 值的条目:
create table Customers ( customer_id INT, first_name VARCHAR(50), last_name VARCHAR(50), address VARCHAR(50), city VARCHAR(50), state VARCHAR(50), zip_code VARCHAR(50), email VARCHAR(50), PRIMARY KEY(customer_id) ); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (1, 'Windham', 'McKevitt', '73 Namekagon Park', 'Washington', 'DC', '20525', 'wmckevitt0@example.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (2, 'Wallis', 'Breawood', '91869 Dexter Parkway', 'Pomona', 'CA', '91797', 'wbreawood1@nhs.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (3, 'Valerie', 'Merkel', '35 Longview Alley', 'Bonita Springs', 'FL', '34135', 'vmerkel2@123-reg.co.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (4, 'Brigid', 'Southwood', '568 Kropf Parkway', 'Indianapolis', 'IN', '46216', 'bsouthwood3@privacy.gov.au'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (5, 'Tucker', 'Du Pre', '521 Bonner Park', 'Miami', 'FL', '33175', 'tdupre4@thetimes.co.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (6, 'Tabbie', 'Maxwaile', '3824 Sunnyside Pass', 'Pittsburgh', 'PA', '15261', 'tmaxwaile5@joomla.org'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (7, 'Cull', 'Stanborough', '8624 Larry Place', 'Boston', 'MA', '02298', 'cstanborough6@alibaba.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (8, 'Homer', 'Park', '8994 Elmside Circle', 'Dallas', 'TX', '75251', 'hpark7@plala.or.jp'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (9, 'Lorelei', 'Bollom', '5737 Manley Circle', 'San Francisco', 'CA', '94121', 'lbollom8@arstechnica.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (10, 'Claudina', 'Thibodeaux', '74 Holy Cross Hill', 'Norfolk', 'VA', '23520', 'cthibodeaux9@mysql.com'); create table Orders ( order_id INT, order_date VARCHAR(50), amount VARCHAR(50), customer_id INT, PRIMARY KEY(order_id) ); insert into Orders (order_id, order_date, amount, customer_id) values (34, '07-11-2020', '$56.34', 1); insert into Orders (order_id, order_date, amount, customer_id) values (12, '07-12-2020', '$23.34', 2); insert into Orders (order_id, order_date, amount, customer_id) values (18, '07-13-2020', '$156.72', 3); insert into Orders (order_id, order_date, amount, customer_id) values (01, '07-14-2020', '$536.12', 4); insert into Orders (order_id, order_date, amount, customer_id) values (89, '07-15-2020', '$99.45', 5); insert into Orders (order_id, order_date, amount, customer_id) values (82, '07-12-2020', '$12.12', 6); insert into Orders (order_id, order_date, amount, customer_id) values (99, '07-11-2020', '$19.52', 7); insert into Orders (order_id, order_date, amount, customer_id) values (23, '07-11-2020', '$88.98', 2); insert into Orders (order_id, order_date, amount, customer_id) values (22, '07-16-2020', '$222.34', 3); insert into Orders (order_id, order_date, amount, customer_id) values (56, '07-16-2020', '$222.34', NULL); select first_name, last_name, order_date, amount from Customers c inner join Orders o on c.customer_id = o.customer_id ORDER BY order_date
RIGHT JOIN（右连接）
右连接获取表 B 中的所有记录（无论它们是否具有 NULL 值）和表 A 中的匹配列。
右连接返回最右边表的所有行和最左边表的匹配行。 RIGHT JOIN 也称为 RIGHT OUTER。这是语法：
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
在这里，我们的客户表是表 A，订单表是表 B。
create table Customers ( customer_id INT, first_name VARCHAR(50), last_name VARCHAR(50), address VARCHAR(50), city VARCHAR(50), state VARCHAR(50), zip_code VARCHAR(50), email VARCHAR(50), PRIMARY KEY(customer_id) ); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (1, 'Windham', 'McKevitt', '73 Namekagon Park', 'Washington', 'DC', '20525', 'wmckevitt0@example.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (2, 'Wallis', 'Breawood', '91869 Dexter Parkway', 'Pomona', 'CA', '91797', 'wbreawood1@nhs.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (3, 'Valerie', 'Merkel', '35 Longview Alley', 'Bonita Springs', 'FL', '34135', 'vmerkel2@123-reg.co.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (4, 'Brigid', 'Southwood', '568 Kropf Parkway', 'Indianapolis', 'IN', '46216', 'bsouthwood3@privacy.gov.au'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (5, 'Tucker', 'Du Pre', '521 Bonner Park', 'Miami', 'FL', '33175', 'tdupre4@thetimes.co.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (6, 'Tabbie', 'Maxwaile', '3824 Sunnyside Pass', 'Pittsburgh', 'PA', '15261', 'tmaxwaile5@joomla.org'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (7, 'Cull', 'Stanborough', '8624 Larry Place', 'Boston', 'MA', '02298', 'cstanborough6@alibaba.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (8, 'Homer', 'Park', '8994 Elmside Circle', 'Dallas', 'TX', '75251', 'hpark7@plala.or.jp'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (9, 'Lorelei', 'Bollom', '5737 Manley Circle', 'San Francisco', 'CA', '94121', 'lbollom8@arstechnica.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (10, 'Claudina', 'Thibodeaux', '74 Holy Cross Hill', 'Norfolk', 'VA', '23520', 'cthibodeaux9@mysql.com'); create table Orders ( order_id INT, order_date VARCHAR(50), amount VARCHAR(50), customer_id INT, PRIMARY KEY(order_id) ); insert into Orders (order_id, order_date, amount, customer_id) values (34, '07-11-2020', '$56.34', 1); insert into Orders (order_id, order_date, amount, customer_id) values (12, '07-12-2020', '$23.34', 2); insert into Orders (order_id, order_date, amount, customer_id) values (18, '07-13-2020', '$156.72', 3); insert into Orders (order_id, order_date, amount, customer_id) values (01, '07-14-2020', '$536.12', 4); insert into Orders (order_id, order_date, amount, customer_id) values (89, '07-15-2020', '$99.45', 5); insert into Orders (order_id, order_date, amount, customer_id) values (82, '07-12-2020', '$12.12', 6); insert into Orders (order_id, order_date, amount, customer_id) values (99, '07-11-2020', '$19.52', 7); insert into Orders (order_id, order_date, amount, customer_id) values (23, '07-11-2020', '$88.98', 2); insert into Orders (order_id, order_date, amount, customer_id) values (22, '07-16-2020', '$222.34', 3); insert into Orders (order_id, order_date, amount, customer_id) values (56, '07-16-2020', '$77.92', NULL); select first_name, last_name, order_date, amount from Customers c right join Orders o on c.customer_id = o.customer_id ORDER BY order_date;
LEFT JOIN（左连接）
左连接类似于右连接。 左连接返回最左边表的所有行和最右边表的匹配行。 以下是语法：
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
在此示例中，列出了 Customers 表中的所有记录（无论它们是否具有 NULL 值）以及 Orders 表中的匹配列。
create table Customers ( customer_id INT, first_name VARCHAR(50), last_name VARCHAR(50), address VARCHAR(50), city VARCHAR(50), state VARCHAR(50), zip_code VARCHAR(50), email VARCHAR(50), PRIMARY KEY(customer_id) ); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (1, 'Windham', 'McKevitt', '73 Namekagon Park', 'Washington', 'DC', '20525', 'wmckevitt0@example.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (2, 'Wallis', 'Breawood', '91869 Dexter Parkway', 'Pomona', 'CA', '91797', 'wbreawood1@nhs.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (3, 'Valerie', 'Merkel', '35 Longview Alley', 'Bonita Springs', 'FL', '34135', 'vmerkel2@123-reg.co.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (4, 'Brigid', 'Southwood', '568 Kropf Parkway', 'Indianapolis', 'IN', '46216', 'bsouthwood3@privacy.gov.au'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (5, 'Tucker', 'Du Pre', '521 Bonner Park', 'Miami', 'FL', '33175', 'tdupre4@thetimes.co.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (6, 'Tabbie', 'Maxwaile', '3824 Sunnyside Pass', 'Pittsburgh', 'PA', '15261', 'tmaxwaile5@joomla.org'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (7, 'Cull', 'Stanborough', '8624 Larry Place', 'Boston', 'MA', '02298', 'cstanborough6@alibaba.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (8, 'Homer', 'Park', '8994 Elmside Circle', 'Dallas', 'TX', '75251', 'hpark7@plala.or.jp'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (9, 'Lorelei', 'Bollom', '5737 Manley Circle', 'San Francisco', 'CA', '94121', 'lbollom8@arstechnica.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (10, 'Claudina', 'Thibodeaux', '74 Holy Cross Hill', 'Norfolk', 'VA', '23520', 'cthibodeaux9@mysql.com'); create table Orders ( order_id INT, order_date VARCHAR(50), amount VARCHAR(50), customer_id INT, PRIMARY KEY(order_id) ); insert into Orders (order_id, order_date, amount, customer_id) values (34, '07-11-2020', '$56.34', 1); insert into Orders (order_id, order_date, amount, customer_id) values (12, '07-12-2020', '$23.34', 2); insert into Orders (order_id, order_date, amount, customer_id) values (18, '07-13-2020', '$156.72', 3); insert into Orders (order_id, order_date, amount, customer_id) values (01, '07-14-2020', '$536.12', 4); insert into Orders (order_id, order_date, amount, customer_id) values (89, '07-15-2020', '$99.45', 5); insert into Orders (order_id, order_date, amount, customer_id) values (82, '07-12-2020', '$12.12', 6); insert into Orders (order_id, order_date, amount, customer_id) values (99, '07-11-2020', '$19.52', 7); insert into Orders (order_id, order_date, amount, customer_id) values (23, '07-11-2020', '$88.98', 2); insert into Orders (order_id, order_date, amount, customer_id) values (22, '07-16-2020', '$222.34', 3); insert into Orders (order_id, order_date, amount, customer_id) values (56, '07-16-2020', '$77.92', NULL); select first_name, last_name, order_date, amount from Customers c left join Orders o on c.customer_id = o.customer_id ORDER BY order_date;
FULL JOIN（全联接）
FULL JOIN 也称为 FULL OUTER JOIN。 这基本上意味着查询将合并数据并从两个表返回记录，无论它们是否具有 NULL 值。
全联接通过组合左右联接的结果（包括所有行）来创建结果集。 对于不匹配的行。 结果集（连接表）将显示 NULL 值。 语法如下：
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;
在 PostgreSQL 中，完整的连接语法有效：
create table Customers ( customer_id INT, first_name VARCHAR(50), last_name VARCHAR(50), address VARCHAR(50), city VARCHAR(50), state VARCHAR(50), zip_code VARCHAR(50), email VARCHAR(50), PRIMARY KEY(customer_id) ); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (1, 'Windham', 'McKevitt', '73 Namekagon Park', 'Washington', 'DC', '20525', 'wmckevitt0@example.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (2, 'Wallis', 'Breawood', '91869 Dexter Parkway', 'Pomona', 'CA', '91797', 'wbreawood1@nhs.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (3, 'Valerie', 'Merkel', '35 Longview Alley', 'Bonita Springs', 'FL', '34135', 'vmerkel2@123-reg.co.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (4, 'Brigid', 'Southwood', '568 Kropf Parkway', 'Indianapolis', 'IN', '46216', 'bsouthwood3@privacy.gov.au'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (5, 'Tucker', 'Du Pre', '521 Bonner Park', 'Miami', 'FL', '33175', 'tdupre4@thetimes.co.uk'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (6, 'Tabbie', 'Maxwaile', '3824 Sunnyside Pass', 'Pittsburgh', 'PA', '15261', 'tmaxwaile5@joomla.org'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (7, 'Cull', 'Stanborough', '8624 Larry Place', 'Boston', 'MA', '02298', 'cstanborough6@alibaba.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (8, 'Homer', 'Park', '8994 Elmside Circle', 'Dallas', 'TX', '75251', 'hpark7@plala.or.jp'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (9, 'Lorelei', 'Bollom', '5737 Manley Circle', 'San Francisco', 'CA', '94121', 'lbollom8@arstechnica.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (10, 'Claudina', 'Thibodeaux', '74 Holy Cross Hill', 'Norfolk', 'VA', '23520', 'cthibodeaux9@mysql.com'); insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (11, NULL, NULL , '74 Holy Cross Hill', 'Norfolk', 'VA', '23520', NULL); create table Orders ( order_id INT, order_date VARCHAR(50), amount VARCHAR(50), customer_id INT, PRIMARY KEY(order_id) ); insert into Orders (order_id, order_date, amount, customer_id) values (98, '07-01-2020', '$333.33', 11); insert into Orders (order_id, order_date, amount, customer_id) values (12, '07-12-2020', '$23.34', 2); insert into Orders (order_id, order_date, amount, customer_id) values (18, '07-13-2020', '$156.72', 3); insert into Orders (order_id, order_date, amount, customer_id) values (01, '07-14-2020', '$536.12', 4); insert into Orders (order_id, order_date, amount, customer_id) values (89, '07-15-2020', '$99.45', 5); insert into Orders (order_id, order_date, amount, customer_id) values (82, '07-12-2020', '$12.12', 6); insert into Orders (order_id, order_date, amount, customer_id) values (99, '07-11-2020', '$19.52', 7); insert into Orders (order_id, order_date, amount, customer_id) values (23, '07-11-2020', '$88.98', 2); insert into Orders (order_id, order_date, amount, customer_id) values (22, '07-16-2020', '$222.34', 3); insert into Orders (order_id, order_date, amount, customer_id) values (56, '07-16-2020', '$77.92', NULL); insert into Orders (order_id, order_date, amount, customer_id) values (34, '07-11-2020', '$56.34', 1); select first_name, last_name, order_date, amount from Customers c full join Orders o on c.customer_id = o.customer_id ORDER BY order_date;
注意：通常不使用全连接，这可以解释为什么 MySQL 不支持全连接。 但是，有一些用例。例如，查看订单与客户无关或未下任何订单的客户的视图条目。