sql select everything from the table that does not appear in a second table

Question

I have three tables -

Users [Id,Name]
Matches_A [Id1,Id2]
Matches_B[Id1,Id2]

I know the user_id of the current user.

For each Id1, in either matches table, there are many entries. That is there are many matches for each user in both tables.

I need to select all of the users who are not in either matches table.

I tried this query -

SELECT * FROM Users 
JOIN Matches_A ON Users.id = Matches_A.id2 
JOIN Matches_B on Users.id = Matches_B.id2
WHERE Matches_A.id1 != $userid
AND Matches_B.id1 != $userid;

This doesn't work.

The problem is that there are entries in the Matches table with the same id2 but different id1s. That means that even if I exclude a row where the id1 matches the userid, that user (id2) could still be returned because there is another row with the same user where the id1 does not match the user_id.

If that made no sense, let me rephrase it. It would be easy to select everything that I don't want to be returned.

SELECT * FROM Users 
JOIN Matches_A ON Users.id = Matches_A.id2 
JOIN Matches_B on Users.id = Matches_B.id2
WHERE Matches_A.id1 != $userid
AND Matches_B.id1 = $userid;

Would return all of the rows that I don't want. How can I write a query that will get me all the other rows.

P.S. It's pretty easy to do this with a sub-query, but I'm worried that will be slow especially with 3 tables.

score 0 · Answer 1 · answered Dec 16 '16 at 04:21

0

Think not in or not exists instead of join:

SELECT *
FROM Users u
WHERE u.id NOT IN (SELECT a.id2 FROM Matches_A) AND 
      u.id NOT IN (SELECT b.id2 FROM Matches_B);

This version assumes that id2 is never NULL in either table.

answered Dec 16 '16 at 04:21

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Dec 16 '16 at 05:14

0

You can try with NOT EXISTS also

SELECT u.*
FROM Users u
WHERE NOT EXISTS (SELECT a.id2 FROM Matches_A a WHERE a.id2 = u.id 
                  UNION ALL 
                  SELECT b.id2 FROM Matches_B b WHERE b.id2 = u.id)

answered Dec 16 '16 at 05:14

Shushil Bohara

5,556
2
15
32

This query is going to be extremely slow if Users has a lot of tuples. – dmg Dec 16 '16 at 05:37
It depends on number of rows returned by subquery, if subquery results is very large then NOT EXISTS execute faster and if subquery results is very small then NOT IN is faster. If we don't have big volume data then you can use any. – Shushil Bohara Dec 16 '16 at 05:50
Your query has to execute one subquery for every tuple in Users. It does not matter how many results the subquery returns. The issue is that it has to execute it. – dmg Dec 16 '16 at 05:52
Please have a look in this http://stackoverflow.com/questions/14190788/subqueries-with-exists-vs-in-mysql and try to limit the comments. If you don't believe in this as well then please do your job in your way but don't suggest others in that way. Thanks – Shushil Bohara Dec 16 '16 at 06:00

score 0 · Answer 3 · answered Dec 16 '16 at 05:36

0

The simplest solution is to use EXCEPT

WITH Subset as (SELECT id from users EXCEPT
                (SELECT id2 from Matches_A 
                 UNION
                 SELECT id2 from Matches_B))
SELECT * FROM Users NATURAL JOIN Subset;

answered Dec 16 '16 at 05:36

dmg

4,231
1
18
24

Do you think EXCEPT works in MYSQL ?? Try to read the question properly and have a look in TAG as well then only try to answer of any question. – Shushil Bohara Dec 16 '16 at 06:03
I guess not. That is the loss of mysql users. :) – dmg Dec 16 '16 at 06:05

sql select everything from the table that does not appear in a second table

3 Answers3