sql联接查询

Joins combine two or more tables together based on the corresponding common column among them.

Join根据其中的对应公共列将两个或多个表组合在一起。

Note: All the below mentioned queries are executed with reference to MySQL Database.

注意： 下面提到的所有查询都是参照MySQL数据库执行的。

---

---

SQL连接的类型 (Types of SQL Joins)

• INNER JOIN
  内部联接
• LEFT OUTER JOIN
  左外连接
• RIGHT OUTER JOIN
  右外连接
• FULL OUTER JOIN
  全外连接
• SELF JOIN
  自我加入

---

---

1.内联 (1. INNER JOIN)

SQL INNER JOIN basically serves as the intersection of two or more tables. It returns elements that are common to both of the tables.

SQL INNER JOIN基本上用作两个或多个表的交集。 它返回两个表共有的元素。

Let’s use the following tables to serve and understand the purpose of SQL Joins.

让我们使用下表来服务和理解SQL连接的目的。

Table name: Information

表名：信息

Employee Id	Manager Id	Batch
1	1	A
2	2	B
3	5	A

员工ID	经理编号	批量
1个	1个	一个
2	2	乙
3	5	一个

Table name: Manager_Details

表名称：Manager_Details

Manager Id	Name	City	Age
1	Safa Mulani	Pune	52
2	Aman Mulani	Satara	25
3	Divya Trivedi	Mumbai	24

经理编号	名称	市	年龄
1个	萨法·穆拉尼（Safa Mulani）	浦那	52
2	阿曼·穆拉尼（Aman Mulani）	萨塔拉	25
3	迪维亚·特里维迪（Divya Trivedi）	孟买	24

Syntax:

句法：

SELECT Column_NamesFROM Table1INNER JOIN Table2ON Table1.Column_Name = Table2.Column_Name;

Example:

例：

SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.NameFROM InformationINNER JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;

Output:

输出：

1	1	Safa Mulani2	2	Aman Mulani

---

---

2.左外连接 (2. LEFT OUTER JOIN)

SQL LEFT OUTER JOIN returns all the elements from the left table(i.e. Table1) and the corresponding matched elements from the right table(i.e. Table2).

SQL LEFT OUTER JOIN返回左表（即Table1）中的所有元素和右表（即Table2）中的对应匹配元素。

If the appropriate match isn’t found, it returns NULL values for the unmatched right table column values.

如果找不到合适的匹配项，则它为不匹配的右表列值返回NULL值。

Syntax:

句法：

SELECT Column_NamesFROM Table1LEFT JOIN Table2ON Table1.Column_Name = Table2.Column_Name;

Example:

例：

SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.NameFROM InformationLEFT JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;

Output:

输出：

1	1	Safa Mulani2	2	Aman Mulani3	NULL	NULL

---

---

3.右外连接 (3. RIGHT OUTER JOIN)

SQL RIGHT OUTER JOIN returns all the elements from the right table(i.e. Table2) and the corresponding matched elements from the left table(i.e. Table1).

SQL RIGHT OUTER JOIN返回右表（即Table2）中的所有元素和左表（即Table1）中的对应匹配元素。

If the appropriate match isn’t found, it returns NULL values for the unmatched left table column values.

如果找不到合适的匹配项，则它为不匹配的左表列值返回NULL值。

Syntax:

句法：

SELECT Column_NamesFROM Table1RIGHT JOIN Table2ON Table1.Column_Name = Table2.Column_Name;

Example:

例：

SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.NameFROM InformationRIGHT JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;

Output:

输出：

1	1	Safa Mulani2	2	Aman MulaniNULL	3	Divya Trivedi

---

---

4.完整的外部连接 (4. FULL OUTER JOIN)

SQL FULL JOIN basically represents all the elements which either matches the elements from the left table or the right table.

SQL FULL JOIN基本上表示与左表或右表中的元素匹配的所有元素。

If the rows of either of the tables do not match each other, still the elements will be represented with NULL values attached to it.

如果两个表中的任何一个表的行都不匹配，则仍将使用NULL值来表示元素。

Syntax:

句法：

SELECT Column_NamesFROM Table1FULL OUTER JOIN Table2ON Table1.Column_Name = Table2.Column_Name;

Most of the Databases such as SQLite, MySQL, etc do not support FULL OUTER Joins.

大多数数据库，例如SQLite ， MySQL等，都不支持FULL OUTER Joins。

In order to serve the purpose, we need to modify the query and make use of UNION clause to provide the functionalities of the FULL OUTER JOIN.

为了达到目的，我们需要修改查询并利用UNION子句来提供FU​​LL OUTER JOIN的功能。

Syntax:

句法：

SELECT Column_NamesFROM Table1LEFT OUTER JOIN Table2ON Table1.Column_Name = Table2.Column_NameUNIONSELECT Column_NamesFROM Table1RIGHT OUTER JOIN Table2ON Table1.Column_Name = Table2.Column_Name;

Example:

例：

SELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.NameFROM InformationLEFT OUTER JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_IdUNIONSELECT Information.Employee_Id, Manager_details.Manager_Id, Manager_details.NameFROM InformationRIGHT OUTER JOIN Manager_details ON Information.Manager_Id = Manager_details.Manager_Id;

Output:

输出：

1	1	Safa Mulani2	2	Aman Mulani3	NULL	NULLNULL	3	Divya Trivedi

---

---

5.自我加入 (5. SELF JOIN)

SQL SELF JOIN represents a join to itself. i.e it renames the table, gives alias names to the corresponding table.

SQL SELF JOIN表示自身的联接。 即，它重命名表，为相应的表提供别名。

Syntax:

句法：

SELECT Column_NameFROM Table1 A, Table1 BWHERE condition;

Here, A and B are the alias names given to the same Table1.

此处，A和B是赋予同一表1的别名。

Example:

例：

SELECT  a.Manager_Id, b.Name, a.City    FROM Manager_details a, Manager_details bWHERE a.Age > 24 AND b.Age > 24;

Output:

输出：

1	Safa Mulani	Pune2	Safa Mulani	Satara1	Aman Mulani	Pune2	Aman Mulani	Satara

---

---

结论 (Conclusion)

Thus, in this article, we have understood the functionalities of SQL Joins.

因此，在本文中，我们了解了SQL Joins的功能。

---

---

参考 (Reference)

翻译自:

sql联接查询