sql join 示例_SQL CROSS JOIN与示例

sql join 示例

In this article, we will learn the SQL CROSS JOIN concept and support our learnings with straightforward examples, which are explained with illustrations.

在本文中，我们将学习SQL CROSS JOIN概念，并通过简单的示例来支持我们的学习，并通过插图进行解释。

介绍 (Introduction)

The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join.

CROSS JOIN用于生成第一张表的每一行与第二张表的每一行的配对组合。 此联接类型也称为笛卡尔联接。

Suppose that we are sitting in a coffee shop and we decide to order breakfast. Shortly, we will look at the menu and we will start thinking of which meal and drink combination could be more tastier. Our brain will receive this signal and begin to generate all meal and drink combinations.

假设我们坐在咖啡店里，我们决定点早餐。 很快，我们将查看菜单，我们将开始考虑哪种饮食组合可能更美味。 我们的大脑将收到此信号，并开始生成所有膳食和饮料组合。

The following image illustrates all menu combinations that can be generated by our brain. The SQL CROSS JOIN works similarly to this mechanism, as it creates all paired combinations of the rows of the tables that will be joined.

下图说明了我们的大脑可以生成的所有菜单组合。 SQL CROSS JOIN类似于此机制，因为它创建将要连接的表行的所有成对组合。

“Please don’t worry, even if you feel a bit hungry now, you can eat whatever you want after reading our article.”

“请别担心，即使您现在有点饿，阅读完我们的文章后，您也可以吃任何想要的东西。”

The main idea of the CROSS JOIN is that it returns the Cartesian product of the joined tables. In the following tip, we will briefly explain the Cartesian product;

CROSS JOIN的主要思想是返回联接表的笛卡尔积。 在下面的技巧中，我们将简要说明笛卡尔积。

Tip: What is the Cartesian Product?

提示： 什么是笛卡尔积？

The Cartesian Product is a multiplication operation in the set theory that generates all ordered pairs of the given sets. Suppose that, A is a set and elements are {a,b} and B is a set and elements are {1,2,3}. The Cartesian Product of these two A and B is denoted AxB and the result will be like the following.

笛卡尔乘积是集合理论中的乘法运算，可生成给定集合的所有有序对。 假设A是一个集合，元素是{a，b}，B是一个集合，元素是{1,2,3}。 这两个A和B的笛卡尔积表示为AxB，结果将如下所示。

AxB ={(a,1), (a,2), (a,3), (b,1), (b,2), (b,3)}

AxB = {（a，1），（a，2），（a，3），（b，1），（b，2），（b，3）}

句法 (Syntax)

The syntax of the CROSS JOIN in SQL will look like the below syntax:

SQL中CROSS JOIN的语法将类似于以下语法：

SELECT ColumnName_1, 
       ColumnName_2, 
       ColumnName_N
FROM [Table_1]
     CROSS JOIN [Table_2]

Or we can use the following syntax instead of the previous one. This syntax does not include the CROSS JOIN keyword; only we will place the tables that will be joined after the FROM clause and separated with a comma.

或者，我们可以使用以下语法代替先前的语法。 此语法不包含CROSS JOIN关键字； 只有我们将要连接的表放在FROM子句之后，并用逗号分隔。

SELECT ColumnName_1, 
       ColumnName_2, 
       ColumnName_N
FROM [Table_1],[Table_2]

The resultset does not change for either of these syntaxes. In addition, we must notice one point about the CROSS JOIN. Unlike the INNER JOIN, LEFT JOIN and FULL OUTER JOIN, the CROSS JOIN does not require a joining condition.

对于这两种语法，结果集都不会更改。 另外，我们必须注意有关CROSS JOIN的一点。 与INNER JOIN，LEFT JOIN和FULL OUTER JOIN不同，CROSS JOIN不需要联接条件。

SQL CROSS JOIN示例： (SQL CROSS JOIN example:)

In this example, we will consider the breakfast menu example again, which we mentioned in the earlier part of the article. Firstly, we will create the two-sample tables which contain the drink and meal names. After then, we will populate them with some sample data.

在此示例中，我们将再次考虑早餐菜单示例，我们在本文的前面部分中已经提到过。 首先，我们将创建两个样本表，其中包含饮料和餐食名称。 之后，我们将使用一些样本数据填充它们。

Through the following query, we will perform these two-steps:

通过以下查询，我们将执行以下两个步骤：

CREATE TABLE Meals(MealName VARCHAR(100))
CREATE TABLE Drinks(DrinkName VARCHAR(100))
INSERT INTO Drinks
VALUES('Orange Juice'), ('Tea'), ('Cofee')
INSERT INTO Meals
VALUES('Omlet'), ('Fried Egg'), ('Sausage')
SELECT *
FROM Meals;
SELECT *
FROM Drinks

The following query will join the Meals and Drinks table with the CROSS JOIN keyword and we will obtain all of the paired combinations of the meal and drink names.

以下查询将膳食和饮料表与CROSS JOIN连接起来 关键字，我们将获得膳食和饮料名称的所有配对组合。

SELECT * FROM Meals 
CROSS JOIN Drinks

The below image illustrates the working principle of the CROSS JOIN.

下图说明了CROSS JOIN的工作原理。

At the same time, we can use the following query in order to obtain the same result set with an alternative syntax without CROSS JOIN.

同时，我们可以使用以下查询来获得具有相同语法的相同结果集，而无需使用CROSS JOIN。

SELECT * FROM Meals 
,Drinks

Tip: The resultset row count will equal to multiplication of tables row counts that will be joined. For the breakfast menu example, the Meals table row count is 3 and the Drinks table row count is 3, so the resultset row count can find with the following calculation.

提示： 结果集行数等于将要连接的表行数的乘积。 对于早餐菜单示例，“餐”表行数为3，“饮料”表行数为3，因此结果集行数可通过以下计算找到。

3 (Meals table row count) x 3 (Drinks table row count) = 9 (Resultset row count)

3 （膳食表行数）x 3 （饮料表行数）= 9 （结果集行数）

CONCAT_WS function will help to concatenate the column expressions. Thus, we can create a more meaningful breakfast menu resultset.

CONCAT_WS函数将有助于连接列表达式。 因此，我们可以创建一个更有意义的早餐菜单结果集。

SELECT CONCAT_WS('-',MealName,DrinkName) AS MenuList
FROM Meals CROSS JOIN 
Drinks

SQL交叉联接和性能注意事项 ( SQL CROSS JOIN and Performance Considerations)

The SQL queries which contain the CROSS JOIN keyword can be very costly. We try to say that these queries have a high potential to consume more resources and can cause performance issues. For the following query, we will analyze the execution plan with ApexSQL Plan. In the generated actual execution plan, we will see a Nested loops operator and when we hover over the mouse on this operator, the detail pop-up window will appear.

包含CROSS JOIN关键字SQL查询可能会非常昂贵。 我们试图说这些查询有很大的潜力消耗更多的资源并可能导致性能问题。 对于以下查询，我们将使用ApexSQL Plan分析执行计划。 在生成的实际执行计划中，我们将看到一个嵌套循环运算符，当我们将鼠标悬停在该运算符上时，将显示详细信息弹出窗口。

In this window, a warning message leaps to our eyes. “No Join Predicate” message specifies that this query can be faced with performance problems. For this reason, the query optimizer warns us about this potential problem. Briefly, when we decide to use the CROSS JOIN in any query, we should consider the number of the tables that will be joined. Such as, when we CROSS JOIN two tables and if the first one contains 1000 rows and the second one contains 1000 rows, the row count of the resultset will be 1.000.000 rows.

在这个窗口中，一条警告信息跳到我们的眼前。 “无连接谓词”消息指定此查询可能面临性能问题。 因此，查询优化器会警告我们有关此潜在问题的信息。 简而言之，当我们决定在任何查询中使用CROSS JOIN时，我们应考虑将要联接的表的数量。 例如，当我们交叉连接两个表时，如果第一个表包含1000行，第二个表包含1000行，则结果集的行数将为1.000.000行。

Tip: CROSS JOIN can only be implemented with Nested Loops, so the following queries will return an error if we force Query Optimizer to use other join types.

提示： CROSS JOIN只能通过嵌套循环实现，因此如果我们强制Query Optimizer使用其他联接类型，则以下查询将返回错误。

SELECT * FROM Meals 
CROSS JOIN Drinks
OPTION (MERGE  JOIN )
 
GO
 
SELECT * FROM Meals 
CROSS JOIN Drinks
OPTION (HASH  JOIN )

结论 (Conclusion)

In this article, we learned SQL CROSS JOIN basics with details and we also mentioned the performance considerations of the CROSS JOIN. When CROSS JOIN is used for tables that have a high number of rows, it might affect the performance negatively.

在本文中，我们详细了解了SQL CROSS JOIN基础知识，并且还提到了CROSS JOIN的性能注意事项。 当CROSS JOIN用于具有大量行的表时，可能会对性能产生负面影响。

翻译自: https://www.sqlshack.com/sql-cross-join-with-examples/

sql join 示例