Returning value from inside exists subquery without joins

Question

I have the following query

SELECT * FROM collection
WHERE id = 1 AND (
    privacy = 0
    OR (privacy = 1 AND
        EXISTS (SELECT * FROM collection_member WHERE collection_id = collection.id AND member_id = 1)
    )
)

This is working fine. However I also want to output result from SELECT * FROM collection_member WHERE collection_id = collection.id AND member_id = 1. What is the most efficient way to achieve this without using joins? (Edit: May be subquery encapsulation?)

If you want the information from collection_member, you have to use a join. Why dont' you want to do that? — , Jul 10 '18 at 06:04
@a_horse_with_no_name Because this is only a simplified version of a bigger query that uses joins to fetch other data. So was wondering if there was another method. — pewpewlasers, Jul 10 '18 at 06:05
There might be many matching members in collection_member. The table name certainly implies that. Which hone do you want? What reasoning do you have for not using a join? — Nick.Mc, Jul 10 '18 at 06:07
@Nick.McDermaid Actually due to `AND member_id = 1`, it should match only one member. — pewpewlasers, Jul 10 '18 at 06:08
Basically you need to use a join to return data from that table, either in the from clause or the select clause. Or you could use a cursor if you really don't want to use a join. That would be madness. — Nick.Mc, Jul 10 '18 at 06:10
@Nick.McDermaid I was thinking along the lines of subquery encapsulation https://stackoverflow.com/a/34955955/1768337 though — pewpewlasers, Jul 10 '18 at 06:13
That example of subquery encapsulation has only one table. You have two and they need to be joined. For simplicity you can pop the entire expression into a field in the select statement. — Nick.Mc, Jul 10 '18 at 06:44

Nick.Mc · Answer 1 · 2018-07-11T06:01:18.070

2

Here is a version that puts the subselect in as a column.

This is still a join but maybe it's closer to what you're after.

SELECT C.* , 
(SELECT MAX(M.MyColumn) 
 FROM collection_member M
 WHERE collection_id = C.id 
 AND M.member_id = 1
 ) MyColumn
FROM collection C
WHERE C.id = 1 
AND (
    C.privacy = 0
    OR (privacy = 1 AND
        EXISTS (
        SELECT * FROM collection_member CM
        WHERE CM.collection_id = C.id AND CM.member_id = 1)
    )
)

Really the only benefits of this approach over performing the JOIN in the from, is that it's a little bit easier to make sure you are not joining many to many and double counting.

The query shown above uses MAX to ensure only one row is returned. This may or may not be the row that you want.

edited Jul 11 '18 at 06:01

answered Jul 10 '18 at 06:47

Nick.Mc

18,304
6
61
91

1

I guess this is the only way. With one caveat that the first subquery can return only 1 column right? – pewpewlasers Jul 11 '18 at 05:05
Yes only one column and also only one row. – Nick.Mc Jul 11 '18 at 05:59
If you need to return more than one column then you need to join the table properly in the `FROM` clause. Regardless, both of these approaches require joins. There's no explanation of why you don't want to do a join. As I mentioned the only non join option is a cursor, which is simply wrong – Nick.Mc Jul 11 '18 at 06:02
The really puzzling part of this question is the constraint "_without_ using joins". Why would you not want to use a join in a database? That's its strength. Subqueries are not a way to avoid joins - you still need to join the subquery and in some cases using a subquery hampers the qurey planner and impacts performance. – Nick.Mc Jul 11 '18 at 06:04

Bohemian · Answer 2 · 2018-07-10T13:56:37.770

You can’t “get values from an exists subquery”.

You have to join to it:

SELECT *
FROM collection
LEFT JOIN collection_member ON privacy = 0
  OR (privacy = 1
      AND collection_id = collection.id
      AND member_id = 1
  )
WHERE id = 1

Of course, unlike your query, collection rows will appear once for every matching row in collection_member. You could deal with this by aggregating all collection_member values.

score 0 · Answer 3 · answered Jul 10 '18 at 08:43

Try this

SELECT c.*, cm.*
FROM collection c
        Left Join collection_member cm on (cm.collection_id = c.collection.id and cm.member_id = 1)
WHERE case
        when c.privacy = 0 Then 1
        when c.privacy = 1 and cm.collection_id is not null Then 1
        else 0
      end = 1
and c.id = 1

Returning value from inside exists subquery without joins

3 Answers3