I ran into this while testing IPv6 address allocations. We use the following type:

to filter out overlapping IP Ranges when we add them to a pool. This generates four subqueries, and uses them inside WHERE NOT EXISTS(subq) in the main INSERT query. If any one of those returns TRUE, we try to insert NULL and fail the whole query.

Those four conditions check:

• The candidate first address is between any existing first / last address
• The candidate last address is between any existing first / last address
• Any existing first address is between the candidate first / last address
• Any existing last address is between the candidate first / last address

That's all fine, but it's overly complicated. We could simplify this whole thing to:

INSERT INTO
    ip_pool_range
SELECT
    <candidate_data>
WHERE NOT EXISTS (
    SELECT 1
    FROM ip_pool_range
    WHERE time_deleted IS NULL
      AND first_address <= $candidate_last_address
      AND last_address >= $candidate_first_address
)

That's logically equivalent, but probably faster and certainly simpler.