How to retrieve closest value based on look-up table?

I'm attempting to create a query that will find the closest value from one table and return its ID into the resulting table.

Below is an example that should describe the situation better.

Sample Data

These two tables will exist in the SQL database.

Main Table

+----+-------------+
| ID | Measurement |
+----+-------------+
|  1 | 0.24        |
|  2 | 0.5         |
|  3 | 0.14        |
|  4 | 0.68        |
+----+-------------+

Look-up Table

+----+---------------+
| ID | Nominal Value |
+----+---------------+
|  1 | 0.1           |
|  2 | 0.2           |
|  3 | 0.3           |
|  4 | 0.4           |
|  5 | 0.5           |
|  6 | 0.6           |
|  7 | 0.7           |
|  8 | 0.8           |
|  9 | 0.9           |
+----+---------------+

Goal

This will be the result of a query. Measurements should not be on the border (0.25 for example).

+----+-------------+-----------+
| ID | Measurement | Lookup ID |
+----+-------------+-----------+
|  1 | 0.24        |         2 |
|  2 | 0.5         |         5 |
|  3 | 0.14        |         1 |
|  4 | 0.68        |         7 |
+----+-------------+-----------+

Is there a query that would be able to return this kind of result?

A couple of queries tested and optimized. All return the same, all are basically standard SQL. (But no RDBMS supports the standard completely.)

The first one uses a LATERAL JOIN, which is missing in Oracle before 12c or MySQL. Test yourself which performs best.

All of them use index-only scans on the lookup table in Postgres. Obviously, lookup.nominal_value needs to be indexed. I suggest to make it UNIQUE because it seems like the column should be unique. That creates the all-important index automatically.
LATERAL JOIN

SELECT m.id, m.measurement, l.nominal_value
FROM   measurement m
JOIN   LATERAL (
   (
   SELECT nominal_value - m.measurement AS diff, nominal_value
   FROM   lookup
   WHERE  nominal_value >= m.measurement
   ORDER  BY nominal_value
   LIMIT  1
   )
   UNION  ALL
   (
   SELECT m.measurement - nominal_value, nominal_value
   FROM   lookup
   WHERE  nominal_value <= m.measurement
   ORDER  by nominal_value DESC
   LIMIT  1
   )
   ORDER  BY 1
   LIMIT  1
   ) l ON true;

All parentheses required for UNION. See:

• Postgres 9.2 select multiple specific rows in one query

Correlated subqueries in a subquery

SELECT id, measurement
     , CASE WHEN hi - measurement > measurement - lo
            THEN lo
            ELSE hi
       END AS nominal_value
FROM  (
   SELECT id, measurement
        , ( SELECT nominal_value
            FROM   lookup
            WHERE  nominal_value >= m.measurement
            ORDER  BY nominal_value
            LIMIT  1) AS hi
         , COALESCE((
            SELECT nominal_value
            FROM   lookup
            WHERE  nominal_value <= m.measurement
            ORDER  by nominal_value DESC
            LIMIT  1), 0) AS lo   -- cover possible NULL values
   FROM   measurement m
   ) sub;

Correlated subqueries in a CTE

WITH cte AS (
   SELECT id, measurement
        , ( SELECT nominal_value
            FROM   lookup
            WHERE  nominal_value >= m.measurement
            ORDER  BY nominal_value
            LIMIT  1) AS hi
        , COALESCE((
            SELECT nominal_value
            FROM   lookup
            WHERE  nominal_value  measurement - lo
            THEN lo
            ELSE hi
       END AS nominal_value
FROM   cte;

Nested correlated subqueries

SELECT id, measurement
     , (SELECT nominal_value FROM (
         (
         SELECT nominal_value - m.measurement, nominal_value
         FROM   lookup
         WHERE  nominal_value >= m.measurement
         ORDER  BY nominal_value
         LIMIT  1
         )
         UNION  ALL
         (
         SELECT m.measurement - nominal_value, nominal_value
         FROM   lookup
         WHERE  nominal_value <= m.measurement
         ORDER  by nominal_value DESC
         LIMIT  1
         )
         ORDER  BY 1
         LIMIT  1
         ) sub
         ) AS nominal_value
FROM   measurement m;

db<>fiddle here

Old sqlfiddle

StackExchange Database Administrators Q#73804, answer score: 8