Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this simple query:

SELECT 
    friend_id
FROM
    default_friend
WHERE
    user_id = 1

And the results are below:

+-----------+
| friend_id |
+-----------+
|      2250 |
|      4901 |
|      7187 |
|      9337 |
|      9843 |
+-----------+

Now I have this query too a bit more complex:

SELECT 
    friend_id, user_id, approved
FROM
    default_friend
WHERE
    user_id = 1
        AND (friend_id IN (SELECT 
            friend_id
        FROM
            default_friend
        WHERE
            user_id = 1))
        AND approved = 0

My question is: will the SELECT into the second query return the same data as the data returned by first query? Meaning friend_id = 2250, 4901, 7187, 9337, 9843 or I need to run the first and the programmatically split the results and build the second one? Also I'll like to know which way is more optimum!

share|improve this question
    
They are not equivalent. Well, it may be now but it won't be when the subquery (SELECT friend_id FROM default_friend WHERE user_id = 1) or the similar one return anything else but the four values: (1,2,3,4) –  ypercube Aug 17 '12 at 14:35
    
@ypercube maybe I do not explain so good. So I'll edit my question now –  ReynierPM Aug 17 '12 at 14:42
    
@ypercube done, could you check please? –  ReynierPM Aug 17 '12 at 14:51
add comment

1 Answer 1

up vote 6 down vote accepted

No, you don't have to run the first query, get the results, build the second one dynamically and then run it.

And you don't need this complex second query either. The simple one will suffice:

SELECT 
    friend_id, user_id, approved
FROM
    default_friend
WHERE
    user_id = 1
  AND
    approved = 0 ;

And you don't really need the user_id and the approved in the SELECT list either. They are going to be 1 and 0 in all rows, aren't they?

So, the question is. Why don't you just keep the original, 1st query, adding only the additional condition (approved = 0) ?:

SELECT 
    friend_id
FROM
    default_friend
WHERE
    user_id = 1
  AND
    approved = 0 ;
share|improve this answer
    
+1 for the user_id you're right I don't needed but in the other hand yes I need the complex query because I need to check if user_id = 1 exists on friend_id column or user_id column from default_friend table –  ReynierPM Aug 17 '12 at 14:58
    
Are you going to use this query as a subquery, inside another more complex one? –  ypercube Aug 17 '12 at 14:58
    
I don't think so because it only allows to get my friends and my non accepted friends. Why? Also I notice duplicates in the result and I don't want those any advice in how to remove? Yes I could use DISTINCT but then when I COUNT() based on the same query results will be different because DISTINCT will return 5 (suppose) and COUNT() will return 10 (the real qty) –  ReynierPM Aug 17 '12 at 15:06
    
yeah I notice why the duplicates, because I'm looking for the same value in both columns so it will return 200,1 and 1,200 need to figure out how to remove them from programation :) –  ReynierPM Aug 17 '12 at 15:09
    
I don't see how this query will give duplicate as results. I would think that (user_id, friend_id) is unique in the table. If not, yes, you can use SELECT DISTINCT. –  ypercube Aug 17 '12 at 15:16
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.