A couple of queries tested and optimized for Postgres 9.3. All return the same, all are basically standard SQL, but no RDBMS supports the standard completely.
In particular, the first one uses a LATERAL JOIN
, which is missing in Oracle or MySQL. Test 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, and because that also 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 -- NULLS LAST is default
LIMIT 1
) l ON TRUE;
All parentheses required for UNION
. Related answer:
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 COALESCE(hi, lo) -- cover all possible NULL values
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
,(SELECT nominal_value
FROM lookup
WHERE nominal_value <= m.measurement
ORDER by nominal_value DESC
LIMIT 1) 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
,(SELECT nominal_value
FROM lookup
WHERE nominal_value <= m.measurement
ORDER by nominal_value DESC
LIMIT 1) AS lo
FROM measurement m
)
SELECT id, measurement
,CASE WHEN hi - measurement > measurement - lo
THEN lo
ELSE COALESCE(hi, lo) -- cover all possible NULL values
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;
SQL Fiddle.