SELECT row based on range from columns in second table
When writing the answer below I wasn't aware of postgres' BETWEEN SYMETRIC, since pointed out by a_horse_with_no_name. That would be the simplest solution and I assume would be as easy for the query planner &amp; runner to use indexes to serve as just BETWEEN.

In my defense, I'm mainly a SQL Server person... The longer answer is hopefully still useful for others in a similar position but not using prosgres, or where cross-DB compatibility is a requirement.



The ends of a BETWEEN comparison are always assumed to be low and higher, in that order. In most cases X BETWEEN Y AND Z it is simple syntactic sugar for X&gt;=Y AND X&lt;=Z and 41.33&gt;=41.35 AND 41.33&lt;=41.30 obviously isn't true.

You could use CASE to swap the values:

ON  lat BETWEEN 
        (CASE WHEN lat_start&lt;lat_end THEN lat_start ELSE lat_end END)
        AND
        (CASE WHEN lat_start&gt;lat_end THEN lat_start ELSE lat_end END)


or the less verbose version suggested by ypercube™ in the comments:

ON (lat BETWEEN lat_start AND lat_end  ) 
OR (lat BETWEEN lat_end   AND lat_start)


but both of those would be inefficient because your join predicate is no longer saragble so will not result in an indexed search. If the number of rows being considered in each case is small because of other filters then this will not be an issue.

Another option is to UNION two comparisons:

SELECT &lt;stuff&gt;
FROM   location 
INNER JOIN 
       segments
       ON lat BETWEEN lat_start AND lat_end
WHERE  lat_start &lt;= lat_end
UNION ALL
SELECT &lt;stuff&gt;
FROM   location 
INNER JOIN 
       segments
       ON lat BETWEEN lat_end AND lat_start
WHERE  lat_start &gt; lat_end
;


(here the extra WHERE in each half stops you getting two rows for instances when lat_start = lat_end, so we can use the more efficient UNION ALL instead of plain UNION without worrying about duplicates in those cases)

This means you get indexed lookups for both queries instead of a scan of segments.
(Technically it would be possible for a query planner to decompose ypercube™'s example into the two lookups like the UNION example, but I'm not aware of a query planner that does this - there is a limit to how much thinking a QP can do before it starts taking too much time planning in the general case, compared to the time spent running the result.)

You could change how you are storing your data and insist that the line/area is always defined bottom-left to top-right (so you have lat_left and lat_right instead of lat_start and lat_end) then your original query will word. If direction of travel is important then you can add that as a separate boolean value: 0 for left-to-right,  1 for right-to-left, or similar.

You might want to look into extensions that exist for postgres specifically designed for geometric (including geological, lat+long based) data types and related queries: https://postgis.net
They may do a chunk of your work for you, after the initial learning curve, especially if you start needing to consider more complex shapes.