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!

Answer 1

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'