简短总结
使用WHERE子句匹配INNER JOIN查询中ON子句所使用的相同条件,可以将INNER JOIN语句重写为CROSS JOIN。
表关系
假设我们有以下post
和post_comment
表:
post
表中有以下记录:
| id | title |
|----|-----------|
| 1 | Java |
| 2 | Hibernate |
| 3 | JPA |
而post_comment
具有以下三行:
| id | review | post_id |
|----|-----------|---------|
| 1 | Good | 1 |
| 2 | Excellent | 1 |
| 3 | Awesome | 2 |
SQL内部连接
SQL JOIN子句允许您关联属于不同表的行。例如,CROSS JOIN将创建一个笛卡尔积,其中包含两个连接表之间所有可能的行组合。
虽然CROSS JOIN在某些场景下很有用,但大多数情况下,您希望根据特定条件连接表格。这就是INNER JOIN发挥作用的地方。
SQL INNER JOIN允许我们基于通过ON子句指定的条件筛选连接两个表的笛卡尔积。
SQL内部连接 - ON“始终为真”条件
如果提供一个“始终为真”的条件,INNER JOIN将不会过滤连接的记录,结果集将包含两个连接表的笛卡尔积。
例如,如果我们执行以下SQL INNER JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 1
我们将获取所有
post
和
post_comment
记录的组合:
| p.id | pc.id |
|---------|------------|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
因此,如果ON子句的条件“始终为真”,INNER JOIN查询就等同于CROSS JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 1
ORDER BY p.id, pc.id
SQL INNER JOIN - ON "always false" condition
如果ON子句的条件是“始终为假”,那么所有连接记录都将被过滤掉,结果集将为空。
因此,如果我们执行以下SQL INNER JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 0
ORDER BY p.id, pc.id
我们不会收到任何结果:
| p.id | pc.id |
|
这是因为上面的查询等价于下面的CROSS JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 0
ORDER BY p.id, pc.id
SQL INNER JOIN - 使用外键和主键列的ON子句
最常见的ON子句条件是将子表中的外键列与父表中的主键列匹配,如下面的查询所示:
SELECT
p.id AS "p.id",
pc.post_id AS "pc.post_id",
pc.id AS "pc.id",
p.title AS "p.title",
pc.review AS "pc.review"
FROM post p
INNER JOIN post_comment pc ON pc.post_id = p.id
ORDER BY p.id, pc.id
执行上述SQL INNER JOIN查询时,我们得到以下结果集:
| p.id | pc.post_id | pc.id | p.title | pc.review |
|---------|------------|------------|------------|-----------|
| 1 | 1 | 1 | Java | Good |
| 1 | 1 | 2 | Java | Excellent |
| 2 | 2 | 3 | Hibernate | Awesome |
因此,只有符合ON子句条件的记录包含在查询结果集中。在我们的情况下,结果集包含所有的
post
以及它们的
post_comment
记录。没有相关联的
post_comment
的
post
行被排除在外,因为它们无法满足ON子句条件。
再次强调,上述SQL INNER JOIN查询等同于以下CROSS JOIN查询:
SELECT
p.id AS "p.id",
pc.post_id AS "pc.post_id",
pc.id AS "pc.id",
p.title AS "p.title",
pc.review AS "pc.review"
FROM post p, post_comment pc
WHERE pc.post_id = p.id
非划掉的行是满足WHERE子句的行,只有这些记录会被包含在结果集中。这是最好的方式来可视化INNER JOIN子句的工作原理。
| p.id | pc.post_id | pc.id | p.title | pc.review |
|------|------------|-------|-----------|-----------|
| 1 | 1 | 1 | Java | Good |
| 1 | 1 | 2 | Java | Excellent |
| 1 | 2 | 3 | Java | Awesome |
| 2 | 1 | 1 | Hibernate | Good |
| 2 | 1 | 2 | Hibernate | Excellent |
| 2 | 2 | 3 | Hibernate | Awesome |
| 3 | 1 | 1 | JPA | Good |
| 3 | 1 | 2 | JPA | Excellent |
| 3 | 2 | 3 | JPA | Awesome |
请注意,这仅适用于INNER JOIN,而不适用于OUTER JOIN。