使用单个查询在 postgres 数据库中搜索多个表？

发布于 2024-10-28 03:52:27 字数 209 浏览 1 评论 0原文

我正在尝试搜索单个数据库中的多个表，但我没有任何运气。

我有两个表，“城市”和“国家”，我想要一个单一的搜索来查找两个/任意一个的结果，

像这样的东西 -

SELECT * FROM cities && countries WHERE name ='New York'

任何帮助都会很棒！

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

懷念過去 2024-11-04 03:52:27

这可以通过 JOIN 或 UNION 子句来完成。取决于您想要的结果。（我在以下示例中对您的模式做出一些假设）：

使用 JOIN

SELECT *
FROM cities
JOIN countries ON (cities.country_id = countries.country_id)
WHERE cities.name = 'New York' 
OR countries.name = 'New York'

与 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'

使用 `NATURAL FULL JOIN`

<如果您“正确”使用 code>NATURAL FULL JOIN，则其行为可能与 UNION 类似，如本博客文章或在此。

SELECT *
FROM 
  (SELECT 'cities' AS source, cities.* FROM cities) cities
NATURAL FULL JOIN 
  (SELECT 'countries' AS source, countries.* FROM countries) countries
WHERE name = 'New York'

This can either be done with a JOIN or a UNION clause. Depending on what you want your result to look like. (I'm making some assumptions about your schema in the following examples):

With a JOIN

SELECT *
FROM cities
JOIN countries ON (cities.country_id = countries.country_id)
WHERE cities.name = 'New York' 
OR countries.name = 'New York'

With a UNION (use `ALL` if you can, for performance reasons)

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'

Using `NATURAL FULL JOIN`

NATURAL FULL JOIN may be used to behave similar to a UNION if you use it "correctly", as shown in this blog post or in this one.

SELECT *
FROM 
  (SELECT 'cities' AS source, cities.* FROM cities) cities
NATURAL FULL JOIN 
  (SELECT 'countries' AS source, countries.* FROM countries) countries
WHERE name = 'New York'

回复收藏 0 原文

~没有更多了~