适合初学者的sql_适用于初学者SQL多重连接示例

适合初学者的sql

In this article, we will learn the SQL multiple joins concept and reinforce our learnings with pretty simple examples, which are explained with illustrations. In relational databases, data is stored in tables. Without a doubt, and most of the time, we need a result set that is formed combining data from several tables. The joins allow us to combine data from two or more tables so that we are able to join data of the tables so that we can easily retrieve data from multiple tables. You might ask yourself how many different types of join exist in SQL Server. The answer is there are four main types of joins that exist in SQL Server. First of all, we will briefly describe them using Venn diagram illustrations:

在本文中，我们将学习SQL多重连接的概念，并通过非常简单的示例来加强我们的学习，并通过插图进行解释。 在关系数据库中，数据存储在表中。 毫无疑问，在大多数情况下，我们需要一个结果集，该结果集是结合了多个表中的数据而形成的。 联接使我们能够合并两个或多个表中的数据，以便能够联接表中的数据，以便我们可以轻松地从多个表中检索数据。 您可能会问自己几个 SQL Server中存在不同类型的联接。 答案是，SQL Server中存在四种主要的联接类型。 首先，我们将使用维恩图插图简要描述它们：

• Inner join returns the rows that match in both tables

  内部联接返回两个表中匹配的行

  

• Left join returns all rows from the left table

  左联接返回左表中的所有行

  

• Right join returns all rows from the right table

  右联接返回右表中的所有行

  

• Full join returns whole rows from both tables

  完全联接返回两个表的全部行

  

If you lack knowledge about the SQL join concept in the SQL Server, you can see the SQL Join types overview and tutorial article.

如果您不了解SQL Server中SQL连接概念，则可以查看SQL连接类型概述和教程文章。

After this short explanatory about the SQL joins types, we will go through the multiple joins.

关于SQL连接类型的简短说明之后，我们将介绍多个连接。

什么是SQL多重联接？ (What are SQL multiple joins?)

Multiple joins can be described as follows; multiple join is a query that contains the same or different join types, which are used more than once. Thus, we gain the ability to combine multiple tables of data in order to overcome relational database issues.

多个连接可以描述如下： 多重联接是包含相同或不同联接类型的查询，多次使用。 因此，我们获得了组合多个数据表以克服关系数据库问题的能力。

示例场景 (Example scenario )

Green-Tree company launched a new campaign for the New Year and made different offers to its online customers. As a result of their campaign, they succeeded in converting some offers to sales. In the following examples, we will uncover the new year campaign data details of the Green-Tree company.

Green-Tree公司为新年推出了新的广告系列，并为其在线客户提供了不同的优惠。 由于他们的竞选活动，他们成功地将一些要约转换为销售。 在以下示例中，我们将揭示Green-Tree公司的新年活动数据详细信息。

The company stores these campaign data details in the following tables. Now, we will create these tables through the following query and populate them with some dummy data:

该公司将这些活动数据详细信息存储在下表中。 现在，我们将通过以下查询创建这些表，并使用一些虚拟数据填充它们：

DROP TABLE IF EXISTS sales
GO
DROP TABLE IF EXISTS orders
GO
DROP TABLE IF EXISTS onlinecustomers
GO
CREATE TABLE onlinecustomers (customerid INT PRIMARY KEY IDENTITY(1,1) ,CustomerName VARCHAR(100) 
,CustomerCity VARCHAR(100) ,Customermail VARCHAR(100))
GO
CREATE TABLE orders (orderId INT PRIMARY KEY IDENTITY(1,1) , customerid INT  ,
ordertotal float ,discountrate float ,orderdate DATETIME)
GO
CREATE TABLE sales (salesId INT PRIMARY KEY IDENTITY(1,1) , 
orderId INT  ,
salestotal FLOAT)
GO
 
 
INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Salvador',N'Philadelphia',N'tyiptqo.wethls@chttw.org')
INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Gilbert',N'San Diego',N'rrvyy.wdumos@lklkj.org')
INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Ernest',N'New York',N'ymuea.pnxkukf@dwv.org')
INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Stella',N'Phoenix',N'xvsfzp.rjhtni@rdn.com')
INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Jorge',N'Los Angeles',N'oykbo.vlxopp@nmwhv.org')
INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Jerome',N'San Antonio',N'wkabc.ofmhetq@gtmh.co')
INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Edward',N'Chicago',N'wguexiymy.nnbdgpc@juc.co')
 
GO
 
INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (3,1910.64,5.49,CAST('03-Dec-2019' AS DATETIME))
INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (4,150.89,15.33,CAST('11-Jun-2019' AS DATETIME))
INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (5,912.55,13.74,CAST('15-Sep-2019' AS DATETIME))
INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (7,418.24,14.53,CAST('28-May-2019' AS DATETIME))
INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (55,512.55,13.74,CAST('15-Jun-2019' AS DATETIME))
INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (57,118.24,14.53,CAST('28-Dec-2019' AS DATETIME))
GO
 
INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (3,370.95)
INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (4,882.13)
INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (12,370.95)
INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (13,882.13)
INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (55,170.95)
INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (57,382.13)

SQL多重联接如何工作？ (How SQL multiple joins work?)

Business problem: Which customers were interested in this New Year campaign?

业务问题 ：哪些客户对该新年广告活动感兴趣？

In order to answer this question, we need to find out the matched rows for all the tables because some customers did not receive an email offer, and some offers could not be converted into a sale. The following Venn diagram will help us to figure out the matched rows which we need. In short, the result of this query should be the intersecting rows of all tables in the query. The grey-colored area specifies these rows in the Venn diagram:

为了回答这个问题，我们需要找出所有表的匹配行，因为有些客户没有收到电子邮件报价，有些报价无法转换为销售。 下面的维恩图将帮助我们找出所需的匹配行。 简而言之，此查询的结果应该是查询中所有表的相交行。 灰色区域指定维恩图中的这些行：

The SQL multiple joins approach will help us to join onlinecustomers, orders, and sales tables. As shown in the Venn diagram, we need to matched rows of all tables. For this reason, we will combine all tables with an inner join clause. The following query will return a result set that is desired from us and will answer the question:

SQL多联接方法将帮助我们联接在线客户 ， 订单和销售表。 如维恩图中所示，我们需要匹配所有表的行。 因此，我们将所有表与一个内部join子句结合在一起。 以下查询将返回我们想要的结果集，并将回答该问题：

SELECT customerName, customercity, customermail, salestotal
FROM onlinecustomers AS oc
   INNER JOIN
   orders AS o
   ON oc.customerid = o.customerid
   INNER JOIN
   sales AS s
   ON o.orderId = s.orderId

At first, we will analyze the query. An inner join clause that is between onlinecustomers and orders tables derived the matched rows between these two tables. The second inner join clause that combines the sales table derived the matched rows from the previous result set. The following colored tables illustration will help us to understand the joined tables data matching in the query. The yellow-colored rows specify matched data between onlinecustomers and orders. On the other hand, only the blue colored rows exist in the sales tables so the query result will be blue colored rows:

首先，我们将分析查询。 内部联接 在线客户和订单表之间的子句派生了这两个表之间的匹配行。 第二个内部join子句结合了销售表，该表从先前的结果集中派生了匹配的行。 下面的彩色表插图将帮助我们了解查询中匹配的联接表数据。 黄色行指定在线客户和订单之间的匹配数据。 另一方面， 销售表中仅存在蓝色行，因此查询结果将是蓝色行：

The result of the query will look like this:

查询结果将如下所示：

SQL多联接中不同联接类型的用法 (Different join types usage in SQL multiple joins)

Business problem: Which offers could not be converted into a sell?

业务问题 ：哪些要约不能转换为卖出？

We can use the different types of joins in a single query so that we can overcome different relational database issues. In this example, we need all rows of the orders table, which are matched to onlinecustomers tables. On the other hand, these rows do not exist in the sales table. The following Venn diagram will help us to figure out the matched rows which we need. The grey-colored area indicates rows which will be the output of the query:

我们可以在单个查询中使用不同类型的联接，以便克服不同的关系数据库问题。 在此示例中，我们需要与在线客户表匹配的订单表的所​​有行。 另一方面，这些行在sales表中不存在。 下面的维恩图将帮助我们找出所需的匹配行。 灰色区域指示将作为查询输出的行：

In the first step, we should combine the onlinecustomers and orders tables through the inner join clause because inner join returns all the matched rows between onlinecustomers and orders tables. In the second step, we will combine the orders table to the sales table through the left join and then filter the null values because we need to eliminate the rows which are stored by the sales table:

第一步，我们应该通过内部联接将onlinecustomers和orders表结合起来 子句，因为内部联接返回onlinecustomers和orders表之间的所有匹配行。 在第二步中，我们将通过左联接将订单表合并到销售表，然后过滤空值，因为我们需要消除销售表存储的行：

SELECT customerName, customercity, customermail, ordertotal,salestotal
FROM onlinecustomers AS c
   INNER JOIN
   orders AS o
   ON c.customerid = o.customerid
   LEFT JOIN
   sales AS s
   ON o.orderId = s.orderId
   WHERE s.salesId IS NULL

The result of the query will look like this:

查询结果将如下所示：

测验 (Quiz)

Question: Please generate the proper query according to the below Venn diagram.

问题：请根据下面的维恩图生成正确的查询。

Answer: As we learned, the full join allows us to return all rows from the combined tables. The answered query will be like the following:

答：据我们了解，完全连接允许 我们返回组合表中的所有行。 回答的查询将如下所示：

SELECT customerName, customercity, customermail, ordertotal,salestotal
FROM onlinecustomers AS c
   FULL JOIN
   orders AS o
   ON c.customerid = o.customerid
   FULL JOIN
   sales AS s
   ON o.orderId = s.orderId

结论 (Conclusion)

In this article, we focused on the SQL multiple joins approach and learned it with detailed examples. Multiple joins allow us to combine more than two tables so that we can overcome different issues in the relational database system. Furthermore, we saw how we could use different join types in a single query.

在本文中，我们重点介绍了SQL多联接方法，并通过详细的示例学习了它。 多个联接使我们能够组合两个以上的表，以便我们可以克服关系数据库系统中的不同问题。 此外，我们看到了如何在单个查询中使用不同的联接类型。

翻译自: https://www.sqlshack.com/sql-multiple-joins-for-beginners-with-examples/

适合初学者的sql