SQL Server:交叉连接和完全外部连接的区别是什么?

What is the difference between CROSS JOIN and FULL OUTER JOIN in SQL Server?

在SQL Server中，交叉连接和完全外部连接有什么区别?

Are they the same, or not? Please explain. When would one use either of these?

它们是一样的吗?请解释一下。什么时候会用到这两个?

10 个解决方案

#1

189

A cross join produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no on clause because you're just joining everything to everything.

一个交叉连接在两个表之间产生一个笛卡尔积，返回所有行的所有可能的组合。它没有on子句，因为你只是把所有东西都连接在一起。

A full outer join is a combination of a left outer and right outer join. It returns all rows in both tables that match the query's where clause, and in cases where the on condition can't be satisfied for those rows it puts null values in for the unpopulated fields.

完整的外连接是左外连接和右外连接的组合。它返回与查询的where子句匹配的两个表中的所有行，并且在不满足on条件的情况下，它为未填充的字段输入空值。

This wikipedia article explains the various types of joins with examples of output given a sample set of tables.

本文通过给出一组表的示例来解释各种类型的连接，并给出输出示例。

#2

One thing that might not always be obvious to some is that a cross join with an empty table (or result set) results in empty table (M x N; hence M x 0 = 0)

对于某些人来说，可能并不总是显而易见的一件事是，与空表(或结果集)的交叉连接导致空表(mxn);因此M x 0 = 0)

A full outer join will always have rows unless both M and N are 0.

一个完整的外部连接总是有行，除非M和N都是0。

#3

I'd like to add one important aspect to other answers, which actually explained this topic to me in the best way:

我想在其他答案中加入一个重要的方面，它实际上以最好的方式向我解释了这个话题:

If 2 joined tables contain M and N rows, then cross join will always produce (M x N) rows, but full outer join will produce from MAX(M,N) to (M + N) rows (depending on how many rows actually match "on" predicate).

如果2个已连接的表包含M和N行，那么cross join将始终生成(M x N)行，但是完整的外部连接将生成从MAX(M,N)到(M + N)行(取决于实际匹配“on”谓词的行数)。

EDIT:

编辑:

From logical query processing perspective, CROSS JOIN does indeed always produce M x N rows. What happens with FULL OUTER JOIN is that both left and right tables are "preserved", as if both LEFT and RIGHT join happened. So rows, not satisfying ON predicate, from both left and right tables are added to the result set.

从逻辑查询处理的角度来看，交叉连接确实总是产生mxn行。完整外部连接的情况是，左表和右表都被“保留”，就好像左表和右表都发生了连接一样。因此，不满足谓词的行，从左表和右表添加到结果集。

#4

Cross join :Cross Joins produce results that consist of every combination of rows from two or more tables. That means if table A has 3 rows and table B has 2 rows, a CROSS JOIN will result in 6 rows. There is no relationship established between the two tables – you literally just produce every possible combination.

交叉连接:交叉连接产生的结果包含来自两个或多个表的每一组行。这意味着如果表A有3行，表B有2行，交叉连接将产生6行。这两个表之间没有任何关系——你只需要生成所有可能的组合。

Full outer Join : A FULL OUTER JOIN is neither "left" nor "right"— it's both! It includes all the rows from both of the tables or result sets participating in the JOIN. When no matching rows exist for rows on the "left" side of the JOIN, you see Null values from the result set on the "right." Conversely, when no matching rows exist for rows on the "right" side of the JOIN, you see Null values from the result set on the "left."

完整的外部连接:完整的外部连接既不是“左”也不是“右”——它两者都是!它包含来自参与联接的两个表或结果集的所有行。当在JOIN的“左侧”一侧不存在匹配行时，您将看到“右侧”上的结果集的空值。相反，当连接的“右”端不存在匹配行时，您可以从“左”的结果集中看到空值。

#5

For SQL Server, CROSS JOIN and FULL OUTER JOIN are different. CROSS JOIN is simply Cartesian Product of two tables, irrespective of any filter criteria or any condition.

对于SQL Server，交叉连接和完全外部连接是不同的。交叉连接是两个表的笛卡尔积，不考虑任何过滤标准或任何条件。

FULL OUTER JOIN gives unique result set of LEFT OUTER JOIN and RIGHT OUTER JOIN of two tables. It also needs ON clause to map two columns of tables.

完整的外连接为两个表的左外连接和右外连接提供唯一的结果集。它还需要ON子句来映射两列表。

Table 1 contains 10 rows and Table 2 contains 20 rows with 5 rows matching on specific columns.

表1包含10行，表2包含20行，其中5行与特定列匹配。

Then CROSS JOIN will return 10*20=200 rows in result set.

然后CROSS JOIN将返回10*20=结果集中的200行。

FULL OUTER JOIN will return 25 rows in result set.

完整的外部连接将返回结果集中的25行。

FULL OUTER JOIN (or any other JOIN) always returns result set with less than or equal to Cartesian Product number.

完全外部连接(或任何其他连接)总是返回小于或等于笛卡尔积号的结果集。

Number of rows returned by FULL OUTER JOIN equal to (No. of Rows by LEFT OUTER JOIN) + (No. of Rows by RIGHT OUTER JOIN) - (No. of Rows by INNER JOIN).

完全外部连接返回的行数等于(不。由左外连接的行)+(不。通过右外连接行)-(不。由内连接的行)。

#6

Cross Join: http://www.dba-oracle.com/t_garmany_9_sql_cross_join.htm

交叉连接:http://www.dba-oracle.com/t_garmany_9_sql_cross_join.htm

TLDR: Generates a all possible combinations between 2 tables (Carthesian product)

TLDR:在两个表之间生成所有可能的组合(Carthesian产品)

(Full) Outer Join : http://www.w3schools.com/Sql/sql_join_full.asp

(完整)外部连接:http://www.w3schools.com/Sql/sql_join_full.asp

TLDR: Returns every row in bot tables and matches those results that have the same values

TLDR:返回bot表中的每一行，并匹配具有相同值的结果

#7

Hi they are the same concepts apart from the NULL value returned.

除了返回的空值之外，它们是相同的概念。

See below:

见下文:

declare @table1 table (     col1    int,    col2    int ) 
declare @table2 table (     col1    int,    col2    int )

insert into @table1 select  1, 11   union all select    2, 22   

insert into @table2 select  10, 101 union all select     2, 202

select  *
from    @table1 t1 full outer join @table2 t2
    on  t1.col1 = t2.col1

/* RESULT
col1        col2        col1        col2     
----------- ----------- ----------- ----------- 
NULL        NULL        10          101
2           22          2           202
1           11          NULL        NULL

(3 row(s) affected)
*/
select  *
from    @table1 t1 cross join @table2 t2

/* RESULT 
col1        col2        col1        col2        
----------- ----------- ----------- ----------- 
1           11          10          101
2           22          10          101
1           11          2           202
2           22          2           202

(4 row(s) affected)
*/

#8

Full outer join :

全外连接:

This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

这个连接结合了左外连接和右外连接。当满足条件时，它从任意一个表返回行，当不匹配时返回空值。

image : (http://www.pinaldave.com/bimg/March09UG/outer%20join.jpg)

图片:(http://www.pinaldave.com/bimg/March09UG/outer%20join.jpg)

Cross Join :

交叉连接:

This join is a Cartesian join that does not necessitate any condition to join. The result set contains records that are multiplication of record number from both the tables.

这个连接是一个笛卡尔连接，它不需要任何条件来连接。结果集包含两个表中记录数相乘的记录。

image : (http://www.pinaldave.com/bimg/March09UG/cross%20join%20-%20half.jpg)

图片:(http://www.pinaldave.com/bimg/March09UG/cross%20join%20-%20half.jpg)

#9

Here is an example where both the FULL OUTER JOIN and CROSS JOIN return the same result set without NULL returned. Please note the 1 = 1 in the ON clause for the FULL OUTER JOIN:

这里有一个示例，其中完整的外部连接和交叉连接返回相同的结果集，而没有返回NULL。请注意在完整的外部连接的ON子句中的1 = 1:

declare @table1 table (     col1    int,    col2    int ) 
declare @table2 table (     col1    int,    col2    int )

insert into @table1 select  1, 11   union all select    2, 22   

insert into @table2 select  10, 101 union all select     2, 202

select  *
from    @table1 t1 full outer join @table2 t2
    on  1 = 1

(2 row(s) affected)

(2 row(s) affected)
col1        col2        col1        col2
----------- ----------- ----------- -----------
1           11          10          101
2           22          10          101
1           11          2           202
2           22          2           202

select  *
from    @table1 t1 cross join @table2 t2

col1        col2        col1        col2
----------- ----------- ----------- -----------
1           11          10          101
2           22          10          101
1           11          2           202
2           22          2           202

(4 row(s) affected)

#10

SQL FULL OUTER JOIN

SQL全外连接

The FULL OUTER JOIN returns all rows from the left table (table1) and from the right table (table2) irrespective of the match.

完整的外部连接返回来自左侧表(表1)和右侧表(表2)的所有行，而与匹配无关。
The FULL OUTER JOIN keyword combines the result of both LEFT OUTER JOIN and RIGHT OUTER JOIN

完整的外部连接关键字组合了左外部连接和右外部连接的结果
SQL full outer join is also known as FULL JOIN
SQL full outer join也称为full join。

Reference : http://datasciencemadesimple.com/sql-full-outer-join/

参考:http://datasciencemadesimple.com/sql-full-outer-join/

SQL CROSS JOIN

SQL交叉连接

In SQL CROSS JOIN Each Row of first table is mapped with the each and every row of second table.

在SQL交叉连接中，第一个表的每一行都被映射到第二个表的每一行。
Number of rows produced by a result set of CROSS JOIN operation is equal to number of rows in the first table multiplied by the number of rows in the second table.

交叉连接操作的结果集生成的行数等于第一个表中的行数乘以第二个表中的行数。
CROSS JOIN is also known as Cartesian product / Cartesian join

交叉连接也称为笛卡尔积/笛卡尔连接。
Number of rows in table A is m, Number of rows in table B is n and resultant table will have m*n rows

表A中的行数为m，表B中的行数为n，结果表中的行数为m*n

Reference:http://datasciencemadesimple.com/sql-cross-join/

参考:http://datasciencemadesimple.com/sql-cross-join/

#1

189