Rewrite UNION as JOIN
<p>I have the following table:</p>

<pre><code>CREATE TABLE temp (
    grp         int,
    version     int,
    deleted     boolean not null,
    PRIMARY KEY (grp, version)
);
</code></pre>

<p>For each entity (<code>grp</code>) there can exist multiple versions (<code>version</code>), and the larger the version number the more recently the version was created. A version may be generally hidden (<code>deleted</code>) for various reasons. It is possible that an entire entity should generally be hidden, in which case all <code>version</code>s for a <code>grp</code> will be <code>deleted</code>.</p>

<p>I want to select/join/filter one row for each entity, where that row is either the most recent version that is not deleted, or the most recent version if all of that entity's versions are deleted.</p>

<p>I currently have a solution that uses a union, but I fear the performance will be poor if I attempt to do further joins or filtering on the union, and I would prefer not to have to repeat those joins/filters in each of the unioned queries.</p>

<p>Can the query below be rewritten such that a union is not required?</p>

<pre><code>SELECT
    main.grp
    , main.version
    , main.deleted
--  , current_filter.version
--  , current_filter.deleted
FROM temp AS main
LEFT JOIN temp AS current_filter
    ON (
        current_filter.grp = main.grp
        AND current_filter.version &gt; main.version
        AND NOT current_filter.deleted
    )
WHERE
    current_filter.version IS null
    AND NOT main.deleted
UNION
SELECT
    main.grp
    , main.version
    , main.deleted
--  , current_filter.version
--  , current_filter.deleted
--  , any_not_deleted.version
--  , any_not_deleted.deleted
FROM temp AS main
LEFT JOIN temp AS current_filter
    ON (
        current_filter.grp = main.grp
        AND current_filter.version &gt; main.version
    )
LEFT JOIN temp AS any_not_deleted
    ON (
        any_not_deleted.grp = main.grp
        AND any_not_deleted.version &lt; main.version
        AND NOT any_not_deleted.deleted
    )
WHERE
    current_filter.version IS null
    AND any_not_deleted.version IS null
    AND main.deleted
ORDER BY grp, version
</code></pre>

<p>SQLFiddle: <a href="http://sqlfiddle.com/#!15/f0b7d/1/0" rel="nofollow">http://sqlfiddle.com/#!15/f0b7d/1/0</a></p>