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} " + ")"
Advertisement
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;