How to re-use result from a SELECT statement?

My goal is to re-use the result from a SELECT statement to be used in SQL EXISTS statement.

The general idea looks like this:

SELECT *
FROM table
WHERE col1=1
OR EXISTS (
  SELECT 1    // this is an exact copy of the SELECT statement above.
  FROM table 
  WHERE col=1
)

The actual SQL statement I am trying to reduce:

          "SELECT user_detail.user, user_detail.name, channel_member.role " +
          "FROM user_detail " +
          "JOIN channel_member ON user_detail.user=channel_member.user " +
          "AND channel_member.uuid=#{channelUuid} " +
          "WHERE user_detail.user=#{username} " +
          "OR EXISTS ( " +
          "   SELECT 1" +
          "   FROM user_detail " +
          "   JOIN channel_member ON user_detail.user=channel_member.user " +
          "   AND channel_member.uuid=#{channelUuid} " +
          "   WHERE user_detail.user=#{username} " +
          ")"

Answer

This query:

SELECT *
FROM table
WHERE col1 = 1 OR
      EXISTS (SELECT 1    // this is an exact copy of the SELECT statement above.
              FROM table 
              WHERE col=1
             )

Doesn’t really make sense. It is saying to return all rows if col = 1 is in the table — but then it filters to check if any row has col = 1. So it is equivalent to:

SELECT *
FROM table
WHERE (SELECT 1 FROM table t2 WHERE t2.col = 1);

I strongly suspect that you intend NOT EXISTS — so get everything with 1. If there is no 1 then return everything:

SELECT *
FROM table
WHERE col1 = 1 OR
      NOT EXISTS (SELECT 1    // this is an exact copy of the SELECT statement above.
                  FROM table 
                  WHERE col = 1
                 );

This should work fine with tables — and is in fact probably optimal with the right indexes.

If “table” is really a complex query, then you might consider window functions:

select t.*
from (select t.*,
             sum( col = 1 ) as num_1s
      from t
     ) t
where col = 1 or num_1s = 0;