sqljoin⽰例_SQLCROSSJOIN与⽰例
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?
提⽰：什么是笛卡尔积？sql left join 多表连接
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 CR
OSS 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（膳⾷表⾏数）x3（饮料表⾏数）=9（结果集⾏数）
function will help to concatenate the column expressions. Thus, we can create a more meaningful breakfast menu resultset.
函数将有助于连接列表达式。 因此，我们可以创建⼀个更有意义的早餐菜单结果集。
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 . 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查询可能会⾮常昂贵。 我们试图说这些查询有很⼤的潜⼒消耗更多的资源并可能导致性能问题。 对于以下查询，我们将使⽤分析执⾏计划。 在⽣成的实际执⾏计划中，我们将看到⼀个嵌套循环运算符，当我们将⿏标悬停在该运算符上时，将显⽰详细信息弹出窗⼝。
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.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。