sql语句左连接和右链接_SQL连接语句最终指南：左，右，内部，外部

最新推荐文章于 2023-07-02 00:51:03 发布

cumifi2519

最新推荐文章于 2023-07-02 00:51:03 发布

阅读量554

点赞数

文章标签： mysql java sql 数据库 hive

原文链接：https://www.freecodecamp.org/news/the-ultimate-guide-to-sql-join-statements/

版权

sql语句左连接和右链接

什么是SQL Join语句？ (What is a SQL Join Statement?)

For this guide we’ll discuss the JOIN section of the SQL statement. We will cover its syntax and the different types of joins that SQL enables.

对于本指南，我们将讨论SQL语句的JOIN部分。我们将介绍它的语法以及SQL支持的不同类型的联接。

专注于JoinSQL语法 (SQL syntax with focus on Join)

SELECT col1, col2, col3, etc....
FROM  tableNameOne AS a
JOIN tableNameTwo AS b ON a.primeKey = b.primeKey 
etc...

The JOIN statement could be just JOIN or INNER JOIN, which are the same, or LEFT JOIN (described below).

JOIN语句可以是相同的JOIN或INNER JOIN，也可以是LEFT JOIN(如下所述)。

不同类型的JOIN (Different Types of JOINs)

(INNER) JOIN
(内部联接
Return records that have matching values in both tables
返回两个表中具有匹配值的记录
LEFT (OUTER) JOIN
左(外)联接
Return all records from the left table, and the matched records from the right table
返回左表中的所有记录，以及右表中的匹配记录
RIGHT (OUTER) JOIN
右(外)联接
Return all records from the right table, and the matched records from the left table
从右表返回所有记录，并从左表返回匹配的记录
FULL (OUTER) JOIN
全(外)联接
Return all records when there is a match in either left or right table
当左表或右表中存在匹配项时，返回所有记录

加入 (Join)

The student table will be in the FROM clause so it will be a starting or LEFT table.

学生表将位于FROM子句中，因此它将是起始表或LEFT表。

We’ll JOIN this to the student contact table or RIGHT table.

我们将其加入到学生联系表或RIGHT表中。

You’ll see that all of the students appear that are also in the contact table.

您会看到出现在联系人表中的所有学生。

As shown in the tables below, studentID 9 is in the student table but NOT in the contact table so won’t appear in a join.

如下表所示，studentID 9在学生表中，但不在联系表中，因此不会出现在联接中。

SQL Statement

SQL语句

SELECT a.studentID, a.FullName, a.programOfStudy,
b.`student-phone-cell`, b.`student-US-zipcode`
FROM student AS a
JOIN `student-contact-info` AS b ON a.studentID = b.studentID;

“Joined” data:

“加入”数据：

+-----------+------------------------+------------------+--------------------+--------------------+
| studentID | FullName               | programOfStudy   | student-phone-cell | student-US-zipcode |
+-----------+------------------------+------------------+--------------------+--------------------+
|         1 | Monique Davis          | Literature       | 555-555-5551       |              97111 |
|         2 | Teri Gutierrez         | Programming      | 555-555-5552       |              97112 |
|         3 | Spencer Pautier        | Programming      | 555-555-5553       |              97113 |
|         4 | Louis Ramsey           | Programming      | 555-555-5554       |              97114 |
|         5 | Alvin Greene           | Programming      | 555-555-5555       |              97115 |
|         6 | Sophie Freeman         | Programming      | 555-555-5556       |              97116 |
|         7 | Edgar Frank "Ted" Codd | Computer Science | 555-555-5557       |              97117 |
|         8 | Donald D. Chamberlin   | Computer Science | 555-555-5558       |              97118 |
+-----------+------------------------+------------------+--------------------+--------------------+

左加入 (Left Join)

Using the keyword LEFT before JOIN causes the system to start with the student (LEFT) table but will return NULL from the RIGHT table if there are no rows for the LEFT table student.

在JOIN之前使用关键字LEFT会导致系统从学生表(LEFT)开始，但是如果LEFT表Student没有行，它将从RIGHT表返回NULL。

Note that studentID 9 appears here but the data from the contact table is just shown as NULL.

请注意，此处显示了StudentID 9，但联系表中的数据仅显示为NULL。

SELECT a.studentID, a.FullName, a.programOfStudy,
b.`student-phone-cell`, b.`student-US-zipcode`
FROM student AS a
LEFT JOIN `student-contact-info` AS b ON a.studentID = b.studentID;

+-----------+------------------------+------------------+--------------------+--------------------+
| studentID | FullName               | programOfStudy   | student-phone-cell | student-US-zipcode |
+-----------+------------------------+------------------+--------------------+--------------------+
|         1 | Monique Davis          | Literature       | 555-555-5551       |              97111 |
|         2 | Teri Gutierrez         | Programming      | 555-555-5552       |              97112 |
|         3 | Spencer Pautier        | Programming      | 555-555-5553       |              97113 |
|         4 | Louis Ramsey           | Programming      | 555-555-5554       |              97114 |
|         5 | Alvin Greene           | Programming      | 555-555-5555       |              97115 |
|         6 | Sophie Freeman         | Programming      | 555-555-5556       |              97116 |
|         7 | Edgar Frank "Ted" Codd | Computer Science | 555-555-5557       |              97117 |
|         8 | Donald D. Chamberlin   | Computer Science | 555-555-5558       |              97118 |
|         9 | Raymond F. Boyce       | Computer Science | NULL               |               NULL |
+-----------+------------------------+------------------+--------------------+--------------------+
9 rows in set (0.00 sec)

完整的表格清单供参考 (Complete table listings for reference)

Student table listings

学生桌清单

SELECT a.studentID, a.FullName, sat_score, a.programOfStudy, schoolEmailAdr 
FROM student AS a;

student or LEFT table

学生或左表

+-----------+------------------------+-----------+------------------+------------------------+
| studentID | FullName               | sat_score | programOfStudy   | schoolEmailAdr         |
+-----------+------------------------+-----------+------------------+------------------------+
|         1 | Monique Davis          |       400 | Literature       | Monique@someSchool.edu |
|         2 | Teri Gutierrez         |       800 | Programming      | Teri@someSchool.edu    |
|         3 | Spencer Pautier        |      1000 | Programming      | Spencer@someSchool.edu |
|         4 | Louis Ramsey           |      1200 | Programming      | Louis@someSchool.edu   |
|         5 | Alvin Greene           |      1200 | Programming      | Alvin@someSchool.edu   |
|         6 | Sophie Freeman         |      1200 | Programming      | Sophie@someSchool.edu  |
|         7 | Edgar Frank "Ted" Codd |      2400 | Computer Science | Edgar@someSchool.edu   |
|         8 | Donald D. Chamberlin   |      2400 | Computer Science | Donald@someSchool.edu  |
|         9 | Raymond F. Boyce       |      2400 | Computer Science | Raymond@someSchool.edu |
+-----------+------------------------+-----------+------------------+------------------------+
9 rows in set (0.00 sec)
```sql
SELECT * from `student-contact-info` AS b;

student contact or RIGHT table

学生联系人或权利表

+-----------+----------------------------------+--------------------+--------------------+
| studentID | studentEmailAddr                 | student-phone-cell | student-US-zipcode |
+-----------+----------------------------------+--------------------+--------------------+
|         1 | Monique.Davis@freeCodeCamp.org   | 555-555-5551       |              97111 |
|         2 | Teri.Gutierrez@freeCodeCamp.org  | 555-555-5552       |              97112 |
|         3 | Spencer.Pautier@freeCodeCamp.org | 555-555-5553       |              97113 |
|         4 | Louis.Ramsey@freeCodeCamp.org    | 555-555-5554       |              97114 |
|         5 | Alvin.Green@freeCodeCamp.org     | 555-555-5555       |              97115 |
|         6 | Sophie.Freeman@freeCodeCamp.org  | 555-555-5556       |              97116 |
|         7 | Maximo.Smith@freeCodeCamp.org    | 555-555-5557       |              97117 |
|         8 | Michael.Roach@freeCodeCamp.ort   | 555-555-5558       |              97118 |
+-----------+----------------------------------+--------------------+--------------------+
8 rows in set (0.00 sec)

As with all of these SQL things there is MUCH MORE to them than what’s in this introductory guide.

与所有这些SQL事物一样，它们比本入门指南中的内容要多得多。

I hope this at least gives you enough to get started.

我希望这至少能给您足够的入门。

Please see the manual for your database manager and have fun trying different options yourself.

请参阅数据库管理员的手册，并尝试自己尝试其他选项，这很有趣。

使用例 (Example of use)

For this guide we’ll discuss the SQL RIGHT JOIN.

对于本指南，我们将讨论SQL RIGHT JOIN。

正确加入 (Right Join)

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table(table1) . The result is NULL from the left side, when there is no match.

RIGHT JOIN关键字从右表(table2)返回所有记录，并从左表(table1)返回匹配的记录。如果没有匹配项，则结果从左侧开始为NULL。

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

完整的表格清单供参考 (Complete table listings for reference)

food or LEFT table data

食物或左表数据

+---------+--------------+-----------+------------+
| ITEM_ID | ITEM_NAME    | ITEM_UNIT | COMPANY_ID |
+---------+--------------+-----------+------------+
| 1       | Chex Mix     | Pcs       | 16         |
| 6       | Cheez-It     | Pcs       | 15         |
| 2       | BN Biscuit   | Pcs       | 15         |
| 3       | Mighty Munch | Pcs       | 17         |
| 4       | Pot Rice     | Pcs       | 15         |
| 5       | Jaffa Cakes  | Pcs       | 18         |
| 7       | Salt n Shake | Pcs       |            |
+---------+--------------+-----------+------------+



company or RIGHT table data
``` text
+------------+---------------+--------------+
| COMPANY_ID | COMPANY_NAME  | COMPANY_CITY |
+------------+---------------+--------------+
| 18         | Order All     | Boston       |
| 15         | Jack Hill Ltd | London       |
| 16         | Akas Foods    | Delhi        |
| 17         | Foodies.      | London       |
| 19         | sip-n-Bite.   | New York     |
+------------+---------------+--------------+

To get company name from company table and company ID, item name columns from foods table, the following SQL statement can be used:

要从公司表获取公司名称和公司ID，从食品表获取项目名称列，可以使用以下SQL语句：

SELECT company.company_id,company.company_name,
company.company_city,foods.company_id,foods.item_name
FROM   company
RIGHT JOIN foods
ON company.company_id = foods.company_id;

OUTPUT

输出值

COMPANY_ID COMPANY_NAME              COMPANY_CITY              COMPANY_ID ITEM_NAME
---------- ------------------------- ------------------------- ---------- --------------
18         Order All                 Boston                    18         Jaffa Cakes
15         Jack Hill Ltd             London                    15         Pot Rice
15         Jack Hill Ltd             London                    15         BN Biscuit
15         Jack Hill Ltd             London                    15         Cheez-It
16         Akas Foods                Delhi                     16         Chex Mix
17         Foodies.                  London                    17         Mighty Munch
NULL       NULL                      NULL                      NULL       Salt n Shake

翻译自: https://www.freecodecamp.org/news/the-ultimate-guide-to-sql-join-statements/

sql语句左连接和右链接

cumifi2519

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
sql语句左连接和右链接_SQL连接语句最终指南：左，右，内部，外部

sql语句左连接和右链接什么是SQL Join语句？ (What is a SQL Join Statement?)For this guide we’ll discuss the JOIN section of the SQL statement. We will cover its syntax and the different types of joins that SQL enabl...
复制链接

扫一扫