searching multiple tables in a postgres DB with a single query?

Question

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!

please add an example row of table cities and table countries to clarify what you want exaclty — bw_üezi
– bw_üezi, Commented Mar 30, 2011 at 8:09

Christian Bangert · Accepted Answer · 2021-10-19 06:37:32Z

47

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'

edited Oct 19, 2021 at 6:37

Christian Bangert

7201 gold badge6 silver badges17 bronze badges

answered Mar 30, 2011 at 8:09

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sloreti Over a year ago

It's important to note that for the UNION solution to work, columns with the same names and types have to be selected from each table (as is the case in Lukas's example).

Lukas Eder Over a year ago

@sloreti: Sure, otherwise you can still use that NATURAL FULL JOIN trick I've added to my answer...

Collectives™ on Stack Overflow

searching multiple tables in a postgres DB with a single query?

1 Answer 1

With a JOIN

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

Using `NATURAL FULL JOIN`

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

With a JOIN

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

Using NATURAL FULL JOIN

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

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

Using `NATURAL FULL JOIN`