postgresql 使用单个查询在 postgres 数据库中搜索多个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5483513/
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
searching multiple tables in a postgres DB with a single query?
提问by tjar
I'm trying to search multiple tables in a single database but I'm not having any luck.
我正在尝试在单个数据库中搜索多个表，但我没有任何运气。
I have two tables, Cities and Countries and I want a single search that finds results from both/either
我有两个表，城市和国家，我想要一个单一的搜索，从两者中找到结果
Something like this -
像这样的东西——
SELECT * FROM cities && countries WHERE name ='New York'
Any help would be awesome!
任何帮助都是极好的！
回答by Lukas Eder
This can either be done with a JOINor a UNIONclause. Depending on what you want your result to look like. (I'm making some assumptions about your schema in the following examples):
这可以用 aJOIN或UNION子句来完成。取决于您希望结果的样子。（我在以下示例中对您的架构进行了一些假设）：
With a JOIN
使用 JOIN
SELECT *
FROM cities
JOIN countries ON (cities.country_id = country.id)
WHERE cities.name = 'New York'
OR countries.name = 'New York'
With a UNION (use ALLif you can, for performance reasons)
使用 UNION（ALL出于性能原因，如果可以，请使用）
SELECT cities.name, 'Is a city' AS type
FROM cities
WHERE cities.name = 'New York'
UNION ALL
SELECT countries.name, 'Is a country' AS type
FROM countries
WHERE countries.name = 'New York'
