从表中选择行，其中具有相同id的另一个表中的行在另一列中具有特定值

In MySQL:

在MySQL中：

If we have two tables:

如果我们有两个表：

comments
key    |    value
=================
1      |    foo
2      |    bar
3      |    foobar
4      |    barfoo

and:

和：

meta
comment_key    |    value
=========================
1              |    1
2              |    1
3              |    2
4              |    1

I want to get the comments from the comment table that have a corresponding comment_key in the meta table that have a specific value (the value column in the meta table).

我想从注释表中获取具有特定值（元表中的值列）的元表中具有相应comment_key的注释。

For example, I'd like to select all the rows from the comment table that have a value of 1 in the meta table:

例如，我想从注释表中选择元表中值为1的所有行：

I'd expect these results:

我期待这些结果：

key    |    value
=================
1      |    foo
2      |    bar
4      |    barfoo

And if I were to select all the rows from the comment table that have a value of 2 in the meta table:

如果我要从注释表中选择元表中值为2的所有行：

I'd expect this result:

我期待这个结果：

key    |    value
=================
3      |    foobar

I really hope someone can help, thank you all in advance!

我真的希望有人可以提供帮助，谢谢你们提前！

I think I need to do a join? Any pointers would be great, and if at all possible, a short explanation so I can work out where I was going wrong -> so I'll know for next time!

我想我需要加入？任何指针都会很棒，如果可能的话，还有一个简短的解释，这样我就可以解决我出错的地方 - >所以下次我会知道！

3 个解决方案

#1

I actually wouldn't recommend a JOIN for this — or rather, I'd recommend a "semijoin", which is a relational-algebra concept not directly expressed in SQL. A semijoin is essentially a join where you want to retrieve records from only one table, but with the proviso that they have corresponding records in a different table.

我实际上不会为此推荐JOIN - 或者更确切地说，我推荐一个“semijoin”，这是一个不直接用SQL表达的关系代数概念。半连接本质上是一个连接，您只想从一个表中检索记录，但条件是它们在不同的表中具有相应的记录。

In SQL notation, this concept is expressed indirectly, by using an IN clause, with a subquery:

在SQL表示法中，通过使用带有子查询的IN子句来间接表达此概念：

SELECT key, value
  FROM comments
 WHERE key IN
        ( SELECT comment_key
            FROM meta
           WHERE value = 1
        )
;

(MySQL will actually end up translating that back into a semijoin internally — essentially a sort of degenerate inner-join — but the IN clause is the natural way to express it in raw SQL.)

（MySQL实际上最终会在内部将其转换为半连接 - 实质上是一种退化的内连接 - 但IN子句是在原始SQL中表达它的自然方式。）

#2

You're looking for a plain, vanilla equi-join here.

你在这里寻找一个普通的香草等联盟。

SELECT `comment`.`key`   AS `key`,
       `comment`.`value` AS `value`
    FROM `comments` 
        JOIN `meta` 
            ON `comments`.`key` = `meta`.`comment_key` 
    WHERE `meta`.`value` = 1;

I'm not really sure what sort of advice you're looking for here but you can read more about the topic (not MySQL specific) at Wikipedia's SQL JOIN page.

我不确定你在这里寻找什么样的建议，但你可以在*的SQL JOIN页面上阅读更多关于这个主题的信息（而不是特定于MySQL）。

I'd recommend indexing on comment.key and meta.comment_key with both being PRIMARY KEY indexes assuming that you want there to only be 1 meta row per comment row (PRIMARY KEYs are UNIQUE by definition). If you want to allow more than 1 meta per comment then add a separate index id column to meta and make that the PRIMARY KEY with comment_key just a b-tree index.

我建议在comment.key和meta.comment_key上建立索引，两者都是PRIMARY KEY索引，假设您希望每个注释行只有1个元行（根据定义PRIMARY KEYs是UNIQUE）。如果您希望每个注释允许超过1个元数据，则向元数据库添加一个单独的索引ID列，并将带有comment_key的PRIMARY KEY作为b-tree索引。

I'm also not sure how the performance of this will compare to the "semi-join" answer also listed but, to me, this is the simpler and more natural way to express the query; with only two tables, though, it shouldn't be too challenging for MySQL to optimize.

我也不确定它的性能如何与列出的“半连接”答案相比，但对我来说，这是表达查询的更简单，更自然的方式;但是，只有两个表，MySQL的优化应该不会太具挑战性。

#3

I would use "INNER JOIN" in the following way:

我会用以下方式使用“INNER JOIN”：

SELECT comments.key, comments.value FROM comments 
INNER JOIN meta ON comments.key=meta.comment_key WHERE meta.value = 1;

Cheers! ;-)

干杯! ;-)

#1