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

问题描述

我有两个要加入的表.

我想要类别表中的所有类别以及用户在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_category_subscriptions表中的user_id.

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中是等效的.但是，对于outer join，它们有很大的不同.

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的子集与user_id的1一起加入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屋！