Reverse effect of JOIN

Question

I got two tables :

A list of people;
A list of people I want to ignore.

When I read the list of people, I don't want to see the ignored people in the list.

My current solution is to query a second time the database (to select the people I want to ignore) and remove them from the array I create with PHP. It's working and it's fine.

However, I want to do that in MySQL. I know JOIN will join only if the row exists in the other table. I am looking for something different (won't show the entry IF the row exists).

I have searched in Google but the lack of "keywords" for this gave me no results.

Thanks

which is your database structure? we can't help you without any source code — Mihai Matei, Oct 30 '12 at 16:29

score 7 · Accepted Answer · answered Oct 30 '12 at 16:30

7

SELECT * FROM Person
LEFT OUTER JOIN IgnoredPerson
ON Person.id = IgnoredPerson.id
WHERE IgnoredPerson.id IS null

Explanation:

enter image description here

Exclude the records we don't want from the right side via a where clause

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

answered Oct 30 '12 at 16:30

Matt Ball

354,903
100
647
710

Is the `OUTER` part needed? Isn't this just a `LEFT JOIN`? – gen_Eric Oct 30 '12 at 16:31
@RocketHazmat [`LEFT OUTER JOIN === LEFT JOIN`](http://stackoverflow.com/questions/2809594/basic-question-left-outer-join-vs-left-join), but I like being explicit. – Matt Ball Oct 30 '12 at 16:33
@MattBall: Thanks, I've just always learned it as `LEFT JOIN` :-) – gen_Eric Oct 30 '12 at 17:48

score 2 · Answer 2 · answered Oct 30 '12 at 16:30

2

Without knowing your schema, I'd suggest something along these lines:

SELECT * FROM people WHERE id NOT IN (SELECT person_id FROM ignored_people)

answered Oct 30 '12 at 16:30

bcmcfc

25,966
29
109
181

Marc · Answer 3 · 2012-10-30T16:35:35.447

2

You could try something like this

 SELECT * FROM people p WHERE NOT EXISTS (SELECT i.id FROM ignorePeople i where p.id = i.id )

here's a link about EXISTS in MySql

edited Oct 30 '12 at 16:35

answered Oct 30 '12 at 16:30

Marc

16,170
20
76
119

Reverse effect of JOIN

3 Answers3