postgresql 检查值是否存在于 SELECT 子句中的另一个表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44773500/
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
checking if a value exists in another table within the SELECT clause
提问by Aayush Karki
I want to query names from table1 and also find if a name exists in table2. I have the following query but it doesn't seem to work. Any suggestions what I did wrong?
我想从 table1 查询名称，并查找 table2 中是否存在名称。我有以下查询，但它似乎不起作用。任何建议我做错了什么？
select A.name,
CASE WHEN A.name in (select B.name in table2 B)
THEN 'common'
ELSE 'not common'
END
from table1 A
Please note that I have to get "common" / "uncommon" from the select clause itself. I'm using postgres.
请注意，我必须从 select 子句本身获得“common”/“uncommon”。我正在使用 postgres。
采纳答案by Anuraag Veerapaneni
Using subquery in SELECT CASE will cost more. Use left join instead like below
在 SELECT CASE 中使用子查询会花费更多。使用左连接代替如下所示
select A.name,
CASE WHEN B.name IS NOT NULL
THEN 'common'
ELSE 'not common'
END
from table1 A
left join table2 B
on A.name = B.name
回答by fthiella
I would use EXIST instead of IN:
我会使用 EXIST 而不是 IN：
select
A.name,
CASE
WHEN EXISTS (select *
from table2 B
where B.name = A.name)
THEN 'common'
ELSE 'not common'
END
from
table1 A
回答by sle1306
Just replace the inwith fromin your subquery.
只需替换子查询中的inwith 即可from。
回答by suriruler
select a.name,
case
when a.name in (select distinct name from table2) then 'common'
else 'not common'
end as new
from table1 a
