Is it possible to make this request with a join?

Question

There are 2 tables: - User(id,pseudo) - Link(id1,id2), those 2 columns are FK on id User

I want to select all Users that have no link with the id = 10. That means I want all id of User but only if a Link(10, id2) doesn't exists.

I have try with a left join but the result is not OK

select distinct *
from USER
left join LINK on USER.id = LINK.id1
where LINK.id1 != 10

Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. This is an easily found faq. — philipxy, Oct 10 '19 at 00:00
Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Oct 10 '19 at 00:01

score 1 · Accepted Answer · answered Oct 09 '19 at 14:49

1

An exists query seems to be the most straightforward here:

SELECT u.id, u.pseudo
FROM User u
WHERE NOT EXISTS (SELECT 1 FROM Link l
                  WHERE (l.id1 = u.id AND l.id2 = 10) OR (l.id2 = u.id AND l.id1 = 10));

In plain English, this says to retain every record from the User table such that no relationship exists between this user and id = 10. Note that I check both sides of a relationship in the Link table, assuming we don't know on which side any user might fall.

answered Oct 09 '19 at 14:49

Tim Biegeleisen

502,043
27
286
360

Thanx it's perfect, do you think I could have performance issues with a lot of records? – Vinz Oct 10 '19 at 07:31
You could try adding the index `(id1, id2)` to the `Link` table, which might let your database evaluate the `NOT EXISTS` clause much faster. We can probably tune my answer so that it would be acceptable to you in terms of performance. – Tim Biegeleisen Oct 10 '19 at 07:33

score 1 · Answer 2 · answered Oct 09 '19 at 15:04

Add the condition LINK.id1 = 10 in the ON clause and in the WHERE clause return only the unmatched rows:

select distinct USER.* 
from USER left join LINK 
on USER.id = LINK.id1 and LINK.id1 = 10
where LINK.id1 is null

It is not clear if you want only id1 not to be 10 or id2 also.

Is it possible to make this request with a join?

2 Answers2