SQL JOIN 中的 WHERE 和 ON 有什么区别

morning刘
157 阅读3分钟

在 SQL 中连接表时，您可能在 ON 子句和 WHERE 子句中有条件。许多人对它们之间的区别感到困惑。在本文中，我们将通过首先提醒您 ON 和 WHERE 子句的用途，然后通过示例演示每个子句中应包含哪些类型的条件来讨论该主题。

ON子句和子句都WHERE可以指定条件。但是它们之间有什么区别吗？如果是这样，您应该在什么地方指定 SQL 查询中的条件？一起来一探究竟吧！

想知道如何使用 JOIN 吗？查看我们的交互式SQL JOIN 课程。

ON 与 WHERE 条件

ON 子句的目的是指定连接条件，即定义表应该如何连接。具体来说，您可以定义记录的匹配方式。

相比之下，子句的目的**WHERE是指定过滤条件**，即定义结果集中应保留哪些行。

让我们看一个例子来理解其中的区别。我们有以下两个表格，它们 (1) 列出了我们租赁网站的用户（表格）和（2）列出了可供出租的**users房屋（表格）。houses**

用户
ID姓名注册日期
11简·斯图尔特2020-11-30
12玛丽库珀2015-06-12
13约翰华生2015-01-31
14克里斯蒂安·伍德2018-03-03
15威廉格雷2021-05-12
16布兰登埃文斯2018-05-08
17伊莎贝拉冈萨雷斯2020-12-12
18黛安娜泰勒2020-06-30
19卢克威尔逊2019-11-17
20迈克尔·李2020-02-15
房屋
ID地址城市owner_id卧室
101布鲁克街 5 号加的夫124
102里士满街 1 号加的夫121
103克伦威尔路 23 号利物浦132
104黑斯廷斯路 109 号约克152
105贝德福德路 2 号布里斯托尔161
106皇后街 45 号布里斯托尔163
107梅菲尔德路 34 号加的夫123
SELECT u.id, u.``name``, u.registration_date, h.address, h.city``FROM users u``JOIN houses h``ON u.id = h.owner_id``WHERE u.registration_date < ``'2020-01-01'``;

请注意，我们在 ON 子句和子句中都有条件WHERE：

  • 有了条件，我们指定通过匹配 users 表中的 id 列和houses 中的列ON来连接表owner_id
  • 有了WHERE条件，我们通过只保留 2020 年 1 月 1 日之前注册的用户来过滤结果集。

因此，我们根据目的使用了ONand条件，从而生成了清晰易读的 SQL 查询。WHERE****

这是结果集：

ID姓名注册日期地址城市
12玛丽库珀2015-06-12布鲁克街 5 号加的夫
12玛丽库珀2015-06-12里士满街 1 号加的夫
13约翰华生2015-01-31克伦威尔路 23 号利物浦
16布兰登埃文斯2018-05-08贝德福德路 2 号布里斯托尔
16布兰登埃文斯2018-05-08皇后街 45 号布里斯托尔
12玛丽库珀2015-06-12梅菲尔德路 34 号加的夫

不确定JOIN在我们的 SQL 查询中是如何工作的？通过此交互式SQL JOIN课程练习连接表。

INNER JOIN 中的 ON 和 WHERE 条件

在上面的示例中，我们可以看到如何根据各自的目的和惯例来使用ON和条件。WHERE

但是，知道对于 ，您可以使用 ON 子句同时(INNER) JOINs指定条件和过滤条件是很有用的。JOIN例如，我们可以通过以下 SQL 查询得到与上面相同的结果：

SELECT u.id, u.``name``, u.registration_date, h.address, h.city``FROM users u``JOIN houses h``ON u.id = h.owner_id ``AND u.registration_date < ``'2020-01-01'``;

该查询以相同的方式执行。也就是说，我不建议在同一个子句中混合连接条件和过滤条件。如果比较这两个查询，您会发现第一个查询更具可读性：

  • 遵循第一个查询更容易：首先，您按特定条件连接表，然后按不同条件过滤结果。
  • 当按照规则分隔条件时，整个查询的意图对于外部读者来说会更清楚。

OUTER JOIN 中的 ON 和 WHERE 条件

当涉及到OUTER JOINs（即 、LEFT JOIN和RIGHT JOIN）时，按预期方式FULL JOIN使用ON和条件是至关重要的。WHERE否则，您会得到错误的结果。让我们看一个例子。

同样，我们想要获取在 2020 年 1 月 1 日之前注册的用户列表，以及他们各自的住所。然而，这一次，我们希望保留所有用户，包括那些在我们的租赁网站上没有注册房屋的用户。因此，我们将做一个LEFT JOIN而不是一个JOIN（即，一个INNER JOIN).

我们将看看在 ON 子句中指定过滤条件与在子句中指定过滤条件是否有任何区别WHERE。如果我们遵循规则并按预期使用条件，我们有以下查询：

SELECT u.id, u.``name``, u.registration_date, h.address, h.city``FROM users u``LEFT JOIN houses h``ON u.id = h.owner_id``WHERE u.registration_date < ``'2020-01-01'``;
ID姓名注册日期地址城市
12玛丽库珀2015-06-12布鲁克街 5 号加的夫
12玛丽库珀2015-06-12里士满街 1 号加的夫
13约翰华生2015-01-31克伦威尔路 23 号利物浦
16布兰登埃文斯2018-05-08贝德福德路 2 号布里斯托尔
16布兰登埃文斯2018-05-08皇后街 45 号布里斯托尔
12玛丽库珀2015-06-12梅菲尔德路 34 号加的夫
19卢克威尔逊2019-11-17无效的无效的
14克里斯蒂安·伍德2018-03-03无效的无效的

结果看起来不错。我们得到了我们在初始示例中得到的所有用户。另外，我们还有两个用户，他们在我们网站上没有对应的房子，但是因为LEFT JOIN. 请注意，根据我们的过滤条件，两者都是在 2020 年 1 月 1 日之前注册的。

如果我们在子句中混合连接条件和过滤条件，我们会得到相同的结果吗ON？让我们找出来：

SELECT u.id, u.``name``, u.registration_date, h.address, h.city``FROM users u``LEFT JOIN houses h``ON u.id = h.owner_id ``AND u.registration_date < ``'2020-01-01'``;
ID姓名注册日期地址城市
11简·斯图尔特2020-11-30无效的无效的
12玛丽库珀2015-06-12梅菲尔德路 34 号加的夫
12玛丽库珀2015-06-12里士满街 1 号加的夫
12玛丽库珀2015-06-12布鲁克街 5 号加的夫
13约翰华生2015-01-31克伦威尔路 23 号利物浦
14克里斯蒂安·伍德2018-03-03无效的无效的
15威廉格雷2021-05-12无效的无效的
16布兰登埃文斯2018-05-08皇后街 45 号布里斯托尔
16布兰登埃文斯2018-05-08贝德福德路 2 号布里斯托尔
17伊莎贝拉冈萨雷斯2020-12-12无效的无效的
18黛安娜泰勒2020-06-30无效的无效的
19卢克威尔逊2019-11-17无效的无效的
20迈克尔·李2020-02-15无效的无效的

正如您所看到的，结果是不同的。我们包含了所有用户，甚至包括在 2020 年或 2021 年注册的用户。这是因为LEFT JOIN即使逻辑失败，它也会保留左表中的所有记录ON。所以，在这个例子中，在子句中指定过滤条件ON对我们不起作用。为了获得正确的结果，我们需要按预期指定条件。

有趣的是，有些情况下WHERE条件可以“取消” OUTER JOIN. 例如，假设我们想要列出所有用户及其相应的房屋，但前提是这些房屋有 3 个或更多卧室。

由于我们想保留所有用户，因此我们将使用OUTER JOIN，特别是LEFT JOIN. 我们对卧室数量的要求显然是一个过滤条件。因此，我们会将其包含在WHERE子句中。这是我们的 SQL 查询，其条件已按预期指定：

SELECT u.id, u.``name``, h.address, h.city, h.bedrooms``FROM users u``LEFT JOIN houses h``ON u.id = h.owner_id``WHERE h.bedrooms > 2;

似乎不对，是吗？结果看起来好像我们使用了 anINNER JOIN而不是LEFT JOIN. 没有房屋的用户不包含在结果表中，因为NULL在连接表时他们在 bedrooms 列中。由于NULL值被认为小于 0，因此当我们应用过滤条件（卧室数大于 2）时，相应的行将被删除。

这个问题有两种可能的解决方案：

  • 在子句中添加另一个过滤条件WHERE，例如 bedrooms 是NULL：

    SELECT u.id, u.``name``, h.address, h.city, h.bedrooms``FROM users u``LEFT JOIN houses h``ON u.id = h.owner_id``WHERE h.bedrooms > 2 ``OR h.bedrooms ``is NULL``;

  • 将过滤条件移动到ON子句中：

    SELECT u.id, u.``name``, h.address, h.city, h.bedrooms``FROM users u``LEFT JOIN houses h``ON u.id = h.owner_id ``AND h.bedrooms > 2;

这些查询中的任何一个都会给我们以下结果：

ID姓名地址城市卧室
11简·斯图尔特无效的无效的无效的
12玛丽库珀梅菲尔德路 34 号加的夫3
12玛丽库珀布鲁克街 5 号加的夫4
13约翰华生克伦威尔路 23 号利物浦无效的
14克里斯蒂安·伍德无效的无效的无效的
15威廉格雷无效的无效的无效的
16布兰登埃文斯皇后街 45 号布里斯托尔3
17伊莎贝拉冈萨雷斯无效的无效的无效的
18黛安娜泰勒无效的无效的无效的
19卢克威尔逊无效的无效的无效的
20迈克尔·李无效的无效的无效的

现在你知道了！在 中OUTER JOINs，我们指定条件的方式确实有所不同

avatar
文章
阅读
粉丝