sql内联接外联接三张表_在SQL中联接三个或更多表

柔情只为你懂 2022-12-07 12:59 152阅读 0赞

![1a8da45af977cef49cbdfe4bddacf674.png][]

sql内联接外联接三张表

Here you will learn about joining three tables in sql with example.

在这里，您将通过示例了解有关在sql中联接三个表的信息。

Let us consider three tables Employee, Department and Project. We will see the complete example wherein these 3 tables are joined to produce a result:

让我们考虑雇员，部门和项目三个表。 我们将看到完整的示例，其中将这3个表连接起来以产生结果：

### **表员工** **(****Table Employee****)** ###

This table records the Id of the Employee which is the primary key, the name of the working Employee, the Department Id of the Department they are working in and the Project they are working for.

此表记录了作为主键的Employee ID，工作Employee的名称，他们正在工作的部门的部门ID以及他们正在工作的项目。

*  EId – Primary key which holds Id of the Employee.
    
    EId –保留员工ID的主键。
 *  EName – Name of the Employee.
    
    EName –员工名称。
 *  DId – Department Id of the Department, the Employee is working in.
    
    DId –部门的部门ID，员工正在工作。
 *  PId – Project Id of the project the Employee is working for.
    
    PId –员工正在从事的项目的项目ID。

**Table creation:**

**表创建：**

CREATE TABLE Employee(EId int primary key,  EName varchar(20), DId int, PId int)
     
    INSERT INTO Employee VALUES(1, Ramesh)
     
    INSERT INTO Employee VALUES(2, Somesh)
     
    INSERT INTO Employee VALUES(3, Rajesh)
     
    INSERT INTO Employee VALUES(4, Ram)
     
    INSERT INTO Employee VALUES(5, Ishi)
     
    INSERT INTO Employee VALUES(6, Rekha)
     
    INSERT INTO Employee VALUES(7,Mukesh)

**This table looks like:**

**该表如下所示：**

<table> 
 <tbody> 
  <tr> 
   <td><strong>EId</strong></td> 
   <td><strong>EName</strong></td> 
   <td><strong>DId</strong></td> 
   <td><strong>PId</strong></td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Ramesh</td> 
   <td>3</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Somesh</td> 
   <td>2</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Rajesh</td> 
   <td>1</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Ram</td> 
   <td>3</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Ishi</td> 
   <td>2</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Rekha</td> 
   <td>4</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>Mukesh</td> 
   <td>1</td> 
   <td>5</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <strong>Id</strong> </td> 
   <td> <strong>姓名</strong> </td> 
   <td> <strong>直拨</strong> </td> 
   <td> <strong>标牌</strong> </td> 
  </tr> 
  <tr> 
   <td> 1个 </td> 
   <td> 拉梅什 </td> 
   <td> 3 </td> 
   <td> 1个 </td> 
  </tr> 
  <tr> 
   <td> 2 </td> 
   <td> Somesh </td> 
   <td> 2 </td> 
   <td> 3 </td> 
  </tr> 
  <tr> 
   <td> 3 </td> 
   <td> 拉杰什 </td> 
   <td> 1个 </td> 
   <td> 5 </td> 
  </tr> 
  <tr> 
   <td> 4 </td> 
   <td> 内存 </td> 
   <td> 3 </td> 
   <td> 4 </td> 
  </tr> 
  <tr> 
   <td> 5 </td> 
   <td> 石井 </td> 
   <td> 2 </td> 
   <td> 3 </td> 
  </tr> 
  <tr> 
   <td> 6 </td> 
   <td> 雷卡 </td> 
   <td> 4 </td> 
   <td> 2 </td> 
  </tr> 
  <tr> 
   <td> 7 </td> 
   <td> 穆克什 </td> 
   <td> 1个 </td> 
   <td> 5 </td> 
  </tr> 
 </tbody> 
</table>

### 餐桌部 **(**Table Department**)** ###

This table contains the Department an Employee Its attributes are:

该表包含部门和雇员，其属性为：

*  DId – This is the Department Id of the Department and it is the primary key in this table.
    
    DId –这是部门的部门ID，它是此表中的主键。
 *  DName- This holds the name of the Department.
    
    DName-保留部门名称。

**Table creation:**

**表创建：**

CREATE TABLE Department(DId int primary key, DName varchar(20))
     
    INSERT INTO Department VALUES(1, Manufacturing)
     
    INSERT INTO Department VALUES(2, HR)
     
    INSERT INTO Department VALUES(3, RnD)
     
    INSERT INTO Department VALUES(4, Accounting)
     
    INSERT INTO Department VALUES(5, IT)

<table> 
 <tbody> 
  <tr> 
   <td><strong>DId</strong></td> 
   <td><strong>DName</strong></td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Manufacturing</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>HR</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>RnD</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Accounting</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>IT</td> 
  </tr> 
 </tbody> 
</table>

### 表项目 **(**Table Project**)** ###

Holds the Project name an Employee is dealing in. It contains the following attributes:

拥有一个雇员正在处理的项目名称。它包含以下属性：

*  PId – Project Id, the primary key of the table.
    
    PId –项目ID，表的主键。
 *  PName – Project N
    
    PName –项目N

**Table Creation:**

**表创建：**

CREATE TABLE Project(PId int primary key, PName varchar(20) )
     
    INSERT INTO Project VALUES(1, Machine Learning)
     
    INSERT INTO Project VALUES(2, Taxes)
     
    INSERT INTO Project VALUES(3, AskHR Portal)
     
    INSERT INTO Project VALUES(4, Blockchain)
     
    INSERT INTO Project VALUES(5, CAD)
     
    INSERT INTO Project VALUES(6, PR)

<table> 
 <tbody> 
  <tr> 
   <td><strong>PId</strong></td> 
   <td><strong>PName</strong></td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Machine Learning</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Taxes</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>AskHR Portal</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Blockchain</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>CAD</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>PR</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <strong>标牌</strong> </td> 
   <td> <strong>名称</strong> </td> 
  </tr> 
  <tr> 
   <td> 1个 </td> 
   <td> 机器学习 </td> 
  </tr> 
  <tr> 
   <td> 2 </td> 
   <td> 税收 </td> 
  </tr> 
  <tr> 
   <td> 3 </td> 
   <td> AskHR门户 </td> 
  </tr> 
  <tr> 
   <td> 4 </td> 
   <td> 区块链 </td> 
  </tr> 
  <tr> 
   <td> 5 </td> 
   <td> 电脑辅助设计 </td> 
  </tr> 
  <tr> 
   <td> 6 </td> 
   <td> 公关 </td> 
  </tr> 
 </tbody> 
</table>

Now, we have already created the three tables we need to work on. Let us consider a case where we need to display the Employee Name, the Department Name and the Project Name together, we would require to join the three tables:

现在，我们已经创建了需要处理的三个表。 让我们考虑一下需要同时显示“雇员姓名”，“部门名称”和“项目名称”的情况，我们需要将三个表连接起来：

*  Apply INNER JOIN on the first two tables.
    
    在前两个表上应用INNER JOIN。
 *  Apply INNER JOIN on the resultant of the two tables and the third table.
    
    将INNER JOIN应用于两个表和第三个表的结果。

If, we have more than three tables with us, we can simply extend the same procedure to multiple tables, i.e, take the resultant of (n-1) tables and join with the nth table.

如果我们拥有三个以上的表，则可以简单地将同一过程扩展到多个表，即，取(n-1)个表的结果并与第n个表联接。

As far as this example is concerned, we will have the QUERY as:

就此示例而言，我们将QUERY作为：

SELECT
     
    Employee.EId, Employee.EName, Department.DName, Project.PName
     
    FROM
     
    ( (Employee INNER JOIN Department ON Employee.DId = Department.DId)
     
    INNER JOIN Project ON Employee.PId = Project.PId);

The resultant table would be:

结果表将是：

<table> 
 <tbody> 
  <tr> 
   <td><strong>EId</strong></td> 
   <td><strong>EName</strong></td> 
   <td><strong>DName</strong></td> 
   <td><strong>PName</strong></td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Ramesh</td> 
   <td>RnD</td> 
   <td>Machine Learning</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Somesh</td> 
   <td>HR</td> 
   <td>AskHR Portal</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Rajesh</td> 
   <td>Manufacturing</td> 
   <td>CAD</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Ram</td> 
   <td>Rnd</td> 
   <td>Blockchain</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Ishi</td> 
   <td>HR</td> 
   <td>AskHR Portal</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Rekha</td> 
   <td>Accounting</td> 
   <td>Taxes</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>Mukesh</td> 
   <td>Manufacturing</td> 
   <td>CAD</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <strong>Id</strong> </td> 
   <td> <strong>姓名</strong> </td> 
   <td> <strong>名称</strong> </td> 
   <td> <strong>名称</strong> </td> 
  </tr> 
  <tr> 
   <td> 1个 </td> 
   <td> 拉梅什 </td> 
   <td> n </td> 
   <td> 机器学习 </td> 
  </tr> 
  <tr> 
   <td> 2 </td> 
   <td> Somesh </td> 
   <td> 人力资源 </td> 
   <td> AskHR门户 </td> 
  </tr> 
  <tr> 
   <td> 3 </td> 
   <td> 拉杰什 </td> 
   <td> 制造业 </td> 
   <td> 电脑辅助设计 </td> 
  </tr> 
  <tr> 
   <td> 4 </td> 
   <td> 内存 </td> 
   <td> Rnd </td> 
   <td> 区块链 </td> 
  </tr> 
  <tr> 
   <td> 5 </td> 
   <td> 石井 </td> 
   <td> 人力资源 </td> 
   <td> AskHR门户 </td> 
  </tr> 
  <tr> 
   <td> 6 </td> 
   <td> 雷卡 </td> 
   <td> 会计 </td> 
   <td> 税收 </td> 
  </tr> 
  <tr> 
   <td> 7 </td> 
   <td> 穆克什 </td> 
   <td> 制造业 </td> 
   <td> 电脑辅助设计 </td> 
  </tr> 
 </tbody> 
</table>

Hence, we can conclude that the joining of the table is as easy as it is important. Once we get familiar with joining two tables, we won’t have to worry about joining multiple tables.

因此，我们可以得出结论，表的连接既简单又重要。 一旦我们熟悉了联接两个表，就不必担心联接多个表。

> 翻译自: [https://www.thecrazyprogrammer.com/2019/05/joining-three-or-more-tables-in-sql.html][https_www.thecrazyprogrammer.com_2019_05_joining-three-or-more-tables-in-sql.html]

sql内联接外联接三张表

[1a8da45af977cef49cbdfe4bddacf674.png]: /images/20221123/594de347b4af43b2afb1d5190b159fa4.png
[https_www.thecrazyprogrammer.com_2019_05_joining-three-or-more-tables-in-sql.html]: https://www.thecrazyprogrammer.com/2019/05/joining-three-or-more-tables-in-sql.html