MySQL 加入 where 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1219909/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL join with where clause
提问by mmundiff
I have two tables I want to join.
我有两个要加入的表。
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.
我想要categories 表中的所有类别以及category_subscriptions 表中用户订阅的所有类别。
essentially this is my query so far:
基本上这是我到目前为止的查询：
SELECT *
FROM categories
LEFT JOIN user_category_subscriptions
ON user_category_subscriptions.category_id = categories.category_id
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.
这工作正常，但是我想在查询的末尾添加一个 where 子句，然后基本上使它成为内部/等连接。
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 being a key in both categories table and user_category_subscriptions. user_id residing in the user_category_subscriptions table.
category_id 是类别表和 user_category_subscriptions 中的键。user_id 驻留在 user_category_subscriptions 表中。
thanks
谢谢
回答by Eric
You need to put it in the joinclause, not the where:
你需要把它放在join子句中，而不是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
See, with an inner join, putting a clause in the joinor the whereis equivalent. However, with an outer join, they are vastly different.
请参阅，使用inner join，将子句放在join或 中where是等效的。但是，有了outer join，它们就大不相同了。
As a joincondition, you specify the rowset that you will be joining to the table. This means that it evaluates user_id = 1first, and takes the subset of user_category_subscriptionswith a user_idof 1to join to all of the rows in categories. This will give you all of the rows in categories, while only the categoriesthat this particular user has subscribed to will have any information in the user_category_subscriptionscolumns. Of course, all other categorieswill be populated with nullin the user_category_subscriptionscolumns.
作为join条件，您指定要加入表的行集。这意味着，它计算user_id = 1第一，并采取子集user_category_subscriptions用user_id的1加入到所有的行categories。这将为您提供 中的所有行categories，而只有categories该特定用户订阅的 将在user_category_subscriptions列中包含任何信息。当然，所有其他的categories都将填充null在user_category_subscriptions列中。
Conversely, a whereclause does the join, and thenreduces the rowset. So, this does all of the joins and then eliminates all rows where user_iddoesn't equal 1. You're left with an inefficient way to get an inner join.
相反，where子句执行连接，然后减少行集。因此，这会执行所有连接，然后消除user_id不等于 的所有行1。您只剩下一种低效的方式来获取inner join.
Hopefully this helps!
希望这会有所帮助！
回答by jaffarali
Try this
尝试这个
SELECT *
FROM categories
LEFT JOIN user_category_subscriptions
ON user_category_subscriptions.category_id = categories.category_id
WHERE user_category_subscriptions.user_id = 1
or user_category_subscriptions.user_id is null
