Linux 左外连接和额外的 where 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3677246/
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
Left Outer join and an additional where clause
提问by atamata
I have a join on two tables defined as a left outer join so that all records are returned from the left hand table even if they don't have a record in the right hand table. However I also need to include a where clause on a field from the right-hand table, but.... I still want a row from the left-hand table to be returned for each record in the left-hand table even if the condition in the where clause isn't met. Is there a way of doing this?
我对定义为左外连接的两个表进行了连接，以便所有记录都从左侧表返回，即使它们在右侧表中没有记录。但是，我还需要在右侧表中的字段上包含一个 where 子句，但是......我仍然希望为左侧表中的每条记录返回左侧表中的一行，即使不满足 where 子句中的条件。有没有办法做到这一点？
采纳答案by Charles Bretana
Yes, put the condition (called a predicate) in the join conditions
是的，将条件（称为谓词）放在连接条件中
Select [stuff]
From TableA a
Left Join TableB b
On b.Pk = a.Pk
-- [Put your condition here, like this]
And b.Column = somevalue
回答by Martin Smith
You just need to put the predicate into the JOINcondition. Putting it into the WHEREclause would effectively convert your query to an inner join.
您只需要将谓词放入JOIN条件中即可。将它放入WHERE子句中将有效地将您的查询转换为内部联接。
For Example:
例如：
...
From a
Left Join b on a.id = b.id and b.condition = 'x'
回答by egrunin
SELECT x.fieldA, y.fieldB
FROM x
LEFT OUTER JOIN (select fieldb, fieldc from Y where condition = some_condition)
ON x.fieldc = y.fieldc
回答by Kirill Leontev
select *
from table1 t1
left outer join table2 t2 on t1.id = t2.id
where t1.some_field = nvl(t2.some_field, t1.some_field)
UPD:errr... no. this way:
UPD：呃……不。这边走：
select *
from table1 t1
left outer join table2 t2 on t1.id = t2.id
where some_required_value = nvl(t2.some_field, some_required_value)
nvlis an Oracle syntax which replaces first argument with second in case it is null(which is common for outer joins). You can use ifnullor coalescefor other databases.
nvl是一种 Oracle 语法，它用第二个参数替换第一个参数，以防万一null（这对于外连接很常见）。您可以使用ifnull或coalesce用于其他数据库。
Thus, you compare t2.some_fieldwith your search criteria if it has met join predicate, but if it has not, then you just return row from table1, because some_required_valuecompared to itself will always be true (unless it is null, however - null = nullyields null, neither truenot false.
因此，t2.some_field如果它满足连接谓词，您就与搜索条件进行比较，但如果没有，那么您只需从 返回行table1，因为some_required_value与自身相比将始终为真（除非它是null，然而 -null = null产量null，两者true都不是false。
回答by Kjetil Watnedal
You can use
您可以使用
WHERE (right_table.column=value OR right_table.column IS NULL)
This will return all rows from table 1 and table 2, but only where table 1 does not have a corresponding row in table 2 or the corresponding row in table 2 matches your criteria.
这将返回表 1 和表 2 中的所有行，但仅当表 1 在表 2 中没有相应的行或表 2 中的相应行符合您的条件时才返回。
