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
11
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
11
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时,才应排除连接结果集的整行。