MySQL 加入 where 子句 [英] MySQL join with where clause

问题描述

我有两张桌子要加入.

我想要categories 表中的所有类别以及category_subscriptions 表中用户订阅的所有类别.

I want all of the categories in the categories table and also all of the categories subscribed to by a user in the category_subscriptions table.

基本上这是我目前的查询:

essentially this is my query so far:

SELECT *
FROM categories
LEFT JOIN user_category_subscriptions 
     ON user_category_subscriptions.category_id = categories.category_id

这很好用，但是我想在查询的末尾添加一个 where 子句，然后基本上使它成为内部/等连接.

This works fine however I want to add a where clause on the end of the query which then essentially makes it an inner/equi join.

   SELECT *
    FROM categories
    LEFT JOIN user_category_subscriptions 
         ON user_category_subscriptions.category_id = categories.category_id 
   WHERE user_category_subscriptions.user_id = 1

如何仅使用一个查询获取所有类别以及特定用户订阅的所有类别?

How do I get all the categories as well as all the categories subscribed to by a particular user using only one query?

category_id 是类别表和 user_category_subscriptions 中的一个键.user_id 驻留在 user_category_subscriptions 表中.

category_id being a key in both categories table and user_category_subscriptions. user_id residing in the user_category_subscriptions table.

谢谢

推荐答案

你需要把它放在join子句中，而不是where:

You need to put it in the join clause, not the where:

SELECT *
FROM categories
LEFT JOIN user_category_subscriptions ON 
    user_category_subscriptions.category_id = categories.category_id
    and user_category_subscriptions.user_id =1

看，使用 inner join，在 join 或 where 中放置一个子句是等效的.但是，使用外连接，它们就大不相同了.

See, with an inner join, putting a clause in the join or the where is equivalent. However, with an outer join, they are vastly different.

作为 join 条件，您指定要连接到表的行集.这意味着它首先评估 user_id = 1，然后将 user_category_subscriptions 的子集与 1 的 user_id 取为加入 categories 中的所有行.这将为您提供 categories 中的所有行，而只有该特定用户订阅的 categories 将在 user_category_subscriptions 中包含任何信息列.当然，所有其他 categories 将在 user_category_subscriptions 列中用 null 填充.

As a join condition, you specify the rowset that you will be joining to the table. This means that it evaluates user_id = 1 first, and takes the subset of user_category_subscriptions with a user_id of 1 to join to all of the rows in categories. This will give you all of the rows in categories, while only the categories that this particular user has subscribed to will have any information in the user_category_subscriptions columns. Of course, all other categories will be populated with null in the user_category_subscriptions columns.

相反，where 子句进行连接，then 减少行集.因此，这会执行所有连接，然后消除 user_id 不等于 1 的所有行.您只能以一种低效的方式获得 inner join.

Conversely, a where clause does the join, and then reduces the rowset. So, this does all of the joins and then eliminates all rows where user_id doesn't equal 1. You're left with an inefficient way to get an inner join.

希望这会有所帮助！

这篇关于MySQL 加入 where 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！