SQL 中的交叉连接与内部连接

CROSS JOIN和 INNER JOIN有什么不同？

交叉连接:

SELECT
Movies.CustomerID, Movies.Movie, Customers.Age,
Customers.Gender, Customers.[Education Level],
Customers.[Internet Connection], Customers.[Marital Status],
FROM
Customers
CROSS JOIN
Movies

内连接:

SELECT
Movies.CustomerID, Movies.Movie, Customers.Age,
Customers.Gender, Customers.[Education Level],
Customers.[Internet Connection], Customers.[Marital Status]
FROM
Customers
INNER JOIN
Movies ON Customers.CustomerID = Movies.CustomerID

哪一个更好，我为什么要用哪一个？

428327 次浏览
小开
最佳答案

交叉连接不会组合这些行，如果每个表中有100行与1对1匹配，就会得到10.000个结果，在相同的情况下，Innerjoin 只会返回100行。

这两个例子将返回相同的结果:

交叉连接

select * from table1 cross join table2 where table1.id = table2.fk_id

内连接

select * from table1 join table2 on table1.id = table2.fk_id

使用最后一种方法

小开

当使用内部联接编写查询时，如果两个表都满足条件，即两个表中的公共列完全匹配，那么记录将从两个表中获取。

在使用 cross join 写查询时，结果就像两个表中的 no 笛卡儿积。例如，如果表1包含2条记录，而表2包含3条记录，那么查询的结果是2 * 3 = 6条记录。

所以不要去交叉连接，直到你需要它。

小开

内部连接

只显示在两个联接表中具有匹配项的行的联接称为内部联接。这是查询和视图设计器中的默认联接。

内连接的语法

SELECT t1.column_name,t2.column_name
FROM table_name1 t1
INNER JOIN table_name2 t2
ON t1.column_name=t2.column_name

交叉连接

一个交叉连接(cross join) ，生成连接中涉及的表的笛卡儿积。笛卡儿积的大小是第一个表中的行数乘以第二个表中的行数。

交叉连接的语法

SELECT * FROM table_name1
CROSS JOIN table_name2

或者我们也可以用另一种方式来写

SELECT * FROM table_name1,table_name2

现在检查下面的关于 Cross join 的查询

例子

SELECT * FROM UserDetails
CROSS JOIN OrderDetails

或者

SELECT * FROM UserDetails, OrderDetails
小开

下面是交叉连接和内部连接的最佳例子。

考虑以下表格

表: Teacher

x------------------------x
| TchrId   | TeacherName |
x----------|-------------x
|    T1    |    Mary     |
|    T2    |    Jim      |
x------------------------x

表: Student

x--------------------------------------x
|  StudId  |    TchrId   | StudentName |
x----------|-------------|-------------x
|    S1    |     T1      |    Vineeth  |
|    S2    |     T1      |    Unni     |
x--------------------------------------x

1. 内连接

内部连接选择同时满足表 的行。

考虑到我们需要找到班主任及其对应的学生。在这种情况下，我们需要应用 JOIN或 INNER JOIN，并将

enter image description here

质疑

SELECT T.TchrId,T.TeacherName,S.StudentName
FROM #Teacher T
INNER JOIN #Student S ON T.TchrId = S.TchrId

结果

x--------------------------------------x
|  TchrId  | TeacherName | StudentName |
x----------|-------------|-------------x
|    T1    |     Mary    |    Vineeth  |
|    T1    |     Mary    |    Unni     |
x--------------------------------------x

2. 交叉连接

Cross join 选择第一个表中的所有行和第二个表中的所有行，并显示为笛卡儿积 ie，包含所有可能性

考虑到我们需要找到学校里所有的老师和学生不分班级的老师，我们需要应用 CROSS JOIN。

enter image description here

质疑

SELECT T.TchrId,T.TeacherName,S.StudentName
FROM #Teacher T
CROSS JOIN #Student S

结果

x--------------------------------------x
|  TchrId  | TeacherName | StudentName |
x----------|-------------|-------------x
|    T2    |     Jim     |    Vineeth  |
|    T2    |     Jim     |    Unni     |
|    T1    |     Mary    |    Vineeth  |
|    T1    |     Mary    |    Unni     |
x--------------------------------------x
小开

请记住，如果添加了 WHERE 子句，则交叉联接的行为就是内部联接。例如，下列 Transact-SQL 查询产生相同的结果集。请参阅 http://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspx

小开

SQLServer 还接受更简单的符号:

SELECT A.F,
B.G,
C.H
FROM TABLE_A A,
TABLE_B B,
TABLE_C C
WHERE A.X = B.X
AND B.Y = C.Y

使用这种更简单的表示法，人们不需要考虑内部连接和交叉连接之间的区别。不是两个“ ON”子句，而是一个单独的“ WHERE”子句。如果在找出哪些“ JOIN”“ ON”子句放在哪里有任何困难，那么放弃“ JOIN”表示法，使用上面更简单的表示法。

这不是作弊。

小开

CROSS JOIN = (INNER) JOIN = 逗号(“ ,”)

SQL CROSS JOIN，(INNER) JOIN 和逗号(“ ,”)之间的唯一区别是(INNER) JOIN 有一个 ON，而 CROSS JOIN 和逗号没有。

中间产物

这三种方法都生成了一个中间概念的 SQL 风格的关系“笛卡尔”产品，也就是交叉连接(cross join) ，它包含每个表中一行的所有可能组合。减少行数的是 ON 和/或 WHERE。SQL Fiddle

SQL Standard 定义 < comma > via product (7.51.B. ii) ，< CROSS JOIN > aka CROSS JOIN via < comma > (7.71.A)和(INNER)加入 < 搜索条件 > 通过 < 逗号 > 加上 WHERE (7.71。B).

正如维基百科所言:

交叉连接
CROSS JOIN 返回联接中表的行笛卡儿积。换句话说，它将生成将第一个表中的每一行与第二个表中的每一行组合在一起的行。

内连接
联接的结果可以定义为首先获取表中所有记录的笛卡儿积(或交叉联接)(将表 a 中的每条记录与表 b 中的每条记录结合起来) ，然后返回满足联接谓词的所有记录的结果。

“隐式联接表示法”只是在 SELECT 语句的 FROM 子句中列出用于联接的表，并使用逗号分隔它们。因此它指定了一个交叉连接

请加入我的答案 “内连接”和“外连接”的区别是什么。

Re OUTER JOIN 和使用 ON vs WHERE 在他们中看到我的答案

为什么要比较表之间的列？

当没有重复行时:

每个表都包含从某个填充[命名-]空格语句模板生成真语句的行。(它从—— 满足——某个 (特征)谓词构成一个真正的 提议。)

  • 基表包含从某个 DBA 给定的语句模板中生成真语句的行:

      /* rows where
customer C.CustomerID has age C.Age and ...
*/
FROM Customers C

  • Join 的中间产品包含从其操作数模板的 AND 中生成真实语句的行:

      /* rows where
customer C.CustomerID has age C.Age and ...
AND movie M.Movie is rented by customer M.CustomerID and ...
*/
FROM Customers C CROSS JOIN Movies M

  • ON & WHERE 条件被导入以提供进一步的模板。值也是满足该模板的行:

      /* rows where
customer C.CustomerID has age C.Age and ...
AND movie M.Movie is rented by customer M.CustomerID and ...
AND C.CustomerID = M.CustomerID
AND C.Age >= M.[Minimum Age]
AND C.Age = 18
*/
FROM Customers C INNER JOIN Movies M
ON C.CustomerID = M.CustomerID
AND C.Age >= M.[Minimum Age]
WHERE C.Age = 18

特别是，比较表之间(SQL)相等性的列意味着模板中联接表部分的产品中保存的行对这些列具有相同(非 NULL)值。只是巧合的是，通常通过表之间的相等性比较来删除大量行——需要和足够的是描述所需的行。

只需为所需行的模板编写 SQL 即可！

关于查询(以及表与条件)的含义，请参阅:
如何从另一个 SQL 表获得两个不同列的匹配数据: 内部连接和/或联合？
是否有任何经验法则来从人类可读的描述构造 SQL 查询？

重载“ cross join”

不幸的是，术语“交叉连接”被用来表示:

  • 中间产品。
  • 交叉连接。
  • (INNER)使用 ON 或 WHERE 进行连接，它不会将一个表中的任何列与另一个表中的任何列进行比较。(因为这往往会返回很多中间产品行。)

这些不同的意思混淆了

使用交叉连接 vs (内部)连接 vs 逗号

通常的惯例是:

  • 只有在不比较表之间的列时才使用 CROSJOIN。也就是说，缺乏比较是有意为之。
  • 只有在比较表之间的列时，才使用(INNER) JOIN 和 ON (可能还有其他条件)
  • 别用逗号。

通常也会为 WHERE 保留不对表对的条件。但是它们可能必须放在(n INNER) JOIN ON 中，以获得右、左或全(外) JOIN 参数的适当行。

对于“不要使用逗号” ，将逗号与显式 JOIN 混合可能会产生误导，因为逗号的优先级较低。但是，考虑到中间产品在 CROSS JOIN、(INNER) JOIN 和逗号的含义中所起的作用，支持上述根本不使用中间产品的约定的理由是站不住脚的。十字连接或逗号就像处于真实条件的(内部)连接。中间产品 ON 和 WHERE 都在相应的谓词中引入 AND。然而，也可以考虑使用 INNER JOIN ON ——比方说，只有在找到一对满足 ON 条件的输入行时才生成输出行——但它仍然返回满足该条件的交叉连接行。ON 曾经在 SQL 中补充逗号的唯一原因是编写 外面 JOIN。当然，一个表达应该表达清楚它的意思; 但是什么是清楚的取决于事物被认为是什么意思。

有两个相交圆的维恩图可以说明同一输入的内连接、左连接、右连接和全连接的 输出行之间的区别。当 ON 无条件为 TRUE 时，内部连接的结果与交叉连接相同。它也可以说明为互联网，联合国和除外的 输入和输出行。当两个输入具有相同的列时，INTERSECT 结果与标准 SQL NATRAL JOIN 相同，而且 EXCEPT 结果与涉及 LEFT & RIGHT JOIN 的某些习惯用法相同。但是它确实说明了(INNER) JOIN 通常是如何工作的。那只是 乍一看似乎可信。它可以为 ON、 PKs (主键)、 FK (外键)和/或 SELECT 的 特殊情况识别输入和/或输出的 零件。你所要做的就是确定 这些圆所代表的集合的元素到底是什么。(乱七八糟的演讲从来没有弄清楚。)请记住，对于连接 输出行与输入行有不同的标题，通常是这样的。并且 SQL 表是 袋子而不是 输入和输出行0行的 设置。

小开

内部连接将给出两个表之间匹配记录的结果，而交叉连接将给出两个表之间可能的组合。

小开

交叉连接(Cross join)和内部连接(inner join)是一样的，只是内部连接的区别在于，我们对笛卡儿积的一些结果进行了布尔筛选

table1
x--------------------------------------x
|  fieldA  |    fieldB   |    fieldC   |
x----------|-------------|-------------x
|    A     |      B      |    option1  |
|    A     |      B1     |    option2  |
x--------------------------------------x


table2
x--------------------------------------x
|  fieldA  |    fieldB   |    fieldC   |
x----------|-------------|-------------x
|    A     |      B      |    optionB1 |
|    A1    |      B1     |    optionB2 |
x--------------------------------------x


cross join
A,B,option1,A,B,optionB1
A,B,option1,A1,B1,optionB2
A,B1,option2,A,B,optionB1
A,B1,option2,A1,B1,optionB2


inner join on field1 (only with the value is the same in both tables)
A,B,option1,A,B,optionB1
A,B1,option2,A,B,optionB1


inner join on field1
A,B,option1,A,B,optionB1

在对数据进行设计时，我们决定只有一个字段用于连接。只连接交叉连接两个表，并且只获得完成特殊布尔表达式的行。

注意，如果我们正在执行连接的字段在两个表中都为 null，那么我们将传递过滤器。由我们或数据库制造商添加额外的规则来避免或允许 null。坚持基本原则，它只是一个交叉连接后面跟一个过滤器。

小开

这取决于您期望的输出。

交叉联接将一个表中的所有行匹配到另一个表中的所有行。内部联接匹配一个或多个字段。如果一个表有10行，另一个表有10行，那么这两个联接的行为将不同。

Cross join 将返回100行，它们之间不存在关联，这就是所谓的笛卡儿积。内部连接将使记录彼此匹配。假设其中一个有一个主键，而另一个中有一个外键，那么将返回10行。

交叉连接的一般用途有限，但是为了完整性而存在，并且描述了在查询中没有添加任何关系的连接表的结果。您可以使用交叉连接来列出单词或类似内容的组合。另一方面，内部联接是最常见的联接。

小开

交叉连接

A 交叉连接意味着生成一个笛卡儿积。

一个笛卡儿积接受两组 a 和 b，并从两组给定的数据中产生所有可能的对记录排列。

例如，假设您有以下 ranks和 suits数据库表:

The ranks and suits tables

ranks有以下几行:

| name  | symbol | rank_value |
|-------|--------|------------|
| Ace   | A      | 14         |
| King  | K      | 13         |
| Queen | Q      | 12         |
| Jack  | J      | 11         |
| Ten   | 10     | 10         |
| Nine  | 9      |  9         |

suits表包含以下记录:

| name    | symbol |
|---------|--------|
| Club    | ♣      |
| Diamond | ♦      |
| Heart   | ♥      |
| Spade   | ♠      |

作为 CROSS JOIN 查询，如下所示:

SELECT
r.symbol AS card_rank,
s.symbol AS card_suit
FROM
ranks r
CROSS JOIN
suits s

将产生 ranks和 suites对的所有可能的排列:

| card_rank | card_suit |
|-----------|-----------|
| A         | ♣         |
| A         | ♦         |
| A         | ♥         |
| A         | ♠         |
| K         | ♣         |
| K         | ♦         |
| K         | ♥         |
| K         | ♠         |
| Q         | ♣         |
| Q         | ♦         |
| Q         | ♥         |
| Q         | ♠         |
| J         | ♣         |
| J         | ♦         |
| J         | ♥         |
| J         | ♠         |
| 10        | ♣         |
| 10        | ♦         |
| 10        | ♥         |
| 10        | ♠         |
| 9         | ♣         |
| 9         | ♦         |
| 9         | ♥         |
| 9         | ♠         |

内连接

另一方面，INNER JOIN 不会返回两个连接数据集的笛卡儿积。

相反，INNER JOIN 从左侧表中获取所有元素，并将它们与右侧表中的记录进行匹配，以便:

  • 如果右侧表中没有匹配的记录，则从结果集中筛选出左侧行
  • 对于右侧表中的任何匹配记录，重复左侧行，就好像该记录与右侧表中的所有相关子记录之间存在笛卡儿积一样。

例如，假设我们在父 post和子 post_comment表之间有一个一对多的表关系，看起来如下:

One-to-many table relationship

现在，如果 post表有以下记录:

| id | title     |
|----|-----------|
| 1  | Java      |
| 2  | Hibernate |
| 3  | JPA       |

post_comments表有这些行:

| id | review    | post_id |
|----|-----------|---------|
| 1  | Good      | 1       |
| 2  | Excellent | 1       |
| 3  | Awesome   | 2       |

如下所示的 INNER JOIN 查询:

SELECT
p.id AS post_id,
p.title AS post_title,
pc.review  AS review
FROM post p
INNER JOIN post_comment pc ON pc.post_id = p.id

将包括所有的 post记录以及所有相关的 post_comments:

| post_id | post_title | review    |
|---------|------------|-----------|
| 1       | Java       | Good      |
| 1       | Java       | Excellent |
| 2       | Hibernate  | Awesome   |

基本上，您可以将 INNER JOIN看作一个经过过滤的 CROSS JOIN，其中只有匹配的记录保存在最终的结果集中。

小开

交叉连接是对两个无条件关系的数学运算，这是 cartesian product。如果您添加一个条件来选择交叉连接中的特定行，那么现在这是一个内部连接。您可以在本文 https://en.wikipedia.org/wiki/Cartesian_product中了解更多关于 cartesian product的信息


提交答案