如何使用连接查询两个表并从一个表中获取所有行，从另一个表中获取相关行？

Simplified for example, I have two tables, groups and items.

例如简化,我有两个表,组和项目。

items (
    id,
    groupId,
    title
)

groups (
    id,
    groupTitle,
    externalURL
)

The regular query I'm goes something like this:

常规查询我是这样的:

SELECT
    i.`id`,
    i.`title`,
    g.`id` as 'groupId',
    g.`groupTitle`,
    g.`externalURL`
FROM
    items i INNER JOIN groups g ON (i.`groupId` = g.`id`)

However I need to modify this now, because all the groups which specify an externalURL will not have any corresponding records in the items table (since they're stored externally). Is it possible to do some sort of join so that the output looks kinda like this:

但是我现在需要修改它,因为指定externalURL的所有组都不会在items表中有任何相应的记录(因为它们存储在外部)。是否可以进行某种连接,以便输出看起来像这样:

items:
id    title    groupId
----------------------
1     Item 1   1
2     Item 2   1

groups
id    groupTitle    externalURL
-------------------------------
1     Group 1       NULL
2     Group 2       something
3     Group 3       NULL

Query output:
id    title    groupId    groupTitle    externalURL
---------------------------------------------------
1     Item 1   1          Group 1       NULL
2     Item 2   1          Group 1       NULL
NULL  NULL     2          Group 2       something

-- note that group 3 didn't show up because it had no items OR externalURL

Is that possible in one SQL query?

这可能在一个SQL查询中吗?

1 个解决方案

#1

This is exactly what an outer join is for: return all the rows from one table, whether or not there is a matching row in the other table. In those cases, return NULL for all the columns of the other table.

这正是外连接的用途:返回一个表中的所有行,无论另一个表中是否存在匹配的行。在这些情况下,为另一个表的所有列返回NULL。

The other condition you can take care of in the WHERE clause.

您可以在WHERE子句中处理的另一个条件。

SELECT
    i.`id`,
    i.`title`,
    g.`id` as 'groupId',
    g.`groupTitle`,
    g.`externalURL`
FROM
    items i RIGHT OUTER JOIN groups g ON (i.`groupId` = g.`id`)
WHERE i.`id` IS NOT NULL OR g.`externalURL` IS NOT NULL;

Only if both i.id and g.externalURL are NULL, then the whole row of the joined result set should be excluded.

仅当i.id和g.externalURL都为NULL时,才应排除连接结果集的整行。

#1