In the fiddle below, I have two tables. The first table contains a column with a numeric value (e.g. int). The second table contains rows of numeric ranges (stored in two columns that hold the “low” and “high” limits of the range).

The Problem

The query in the fiddle works, using a FULL OUTER JOIN to produce the expected results. I can add indices to speed queries, but would appreciate thoughts on how to generate SQL to achieve a more optimal query plan than that provided in the fiddle.


DB Fiddle here

Other Notes

Solutions need not be portable (so long as they work in PostgreSQL), but no third-party solutions, please.


PostgreSQL v11.2

Fedora 29

  • Where's the JOIN you mentioned in the fiddle? – Vérace Apr 16 at 20:09
  • @Vérace Perhaps I’m abusing the term — thought that was implicitly performed by the query planner when not joining on specific column(s). If it makes a difference, could add an identifier column on which to specify a JOIN (in the trivial case, would just use a static value like 1 for each row in the tables) – Whee Apr 16 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.