Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Queries using EXISTS and OR operator

I have 4 tables

    users
    |  id  |Username|
    |   1  |  John  |
    |   2  |  Mike  |
    |   3  |  Alex  |

    user_contacts
    | user_id  |contact_id|
    |   1      |    2     |
    |   1      |    3     |
    |   2      |    3     |

    contact_groups
    | id  |  Group name  |
    |  1  |    Group 1   |  
    |  2  |    Group 2   |
    |  3  |    Group 3   |

    user_contact_groups
    | user_id  |contact_group_id|
    |   1      |    1           |
    |   1      |    2           |
    |   3      |    2           |

what id like to do is to pull users which belongs to Contact Group 1 and 3 or a contact of user 1 (in table:user_contacts). Below is code, but it returns query is empty

SELECT DISTINCT a.* from  users as a 
WHERE EXISTS (SELECT * FROM user_contacts as b 
    WHERE b.user_id = 1) OR 
    (a.id IN (select c.user_id 
         FROM user_contact_groups as c 
            WHERE c.contact_group_id IN (1,3)));
like image 266
user1917451 Avatar asked Dec 20 '12 02:12

user1917451


2 Answers

This is how I would do it, and should be the most optimal:

SELECT DISTINCT u.*
FROM users u
LEFT OUTER JOIN user_contacts c ON u.id = c.contact_id 
    AND c.user_id = 1
LEFT OUTER JOIN user_contact_groups g ON u.user_id = g.user_id 
    AND g.contact_group_id IN (1,3)
WHERE c.id IS NOT NULL OR g.id IS NOT NULL

If you wanted to use EXISTS, you can of course turn this around, but the query may not be able to use indexes (you can remove the DISTICT for this query):

SELECT *
FROM users u
WHERE EXISTS
(
    SELECT 1
    FROM user_contacts c 
    WHERE c.contact_id = u.id
    AND c.user_id = 1
)
OR EXISTS
(
    SELECT 1
    FROM user_contact_groups g 
    WHERE g.user_id = u.user_id
    AND g.contact_group_id IN (1,3)
)

I suggest doing an EXPLAIN and see which one is better for your RDBMS.

like image 83
lc. Avatar answered Sep 23 '22 22:09

lc.


Instead of doing subqueries you could do left joins

SELECT DISTINCT a.*
FROM users as a
LEFT JOIN user_contacts as b ON a.id = b.user_id AND a.id = 1
LEFT JOIN user_contact_groups as c on c.user_id = a.id AND c.contact_group_id IN (1,3)
WHERE b.user_id IS NOT NULL OR c.user_id IS NOT NULL

I don't have SQL in front of me to test this, but I think it'll get the right results

like image 22
Greg Avatar answered Sep 20 '22 22:09

Greg