SQL negation in where clause

Question

Hi I'm trying to solve a problem with negating data I have two tables

Classes:

+----+----------+--+
| id |   name   |  |
+----+----------+--+
|  1 | 'First'  |  |
|  2 | 'Second' |  |
+----+----------+--+

Lists:

+----+----------+--+
| id | id_class |  |
+----+----------+--+
|  1 |        1 |  |
+----+----------+--+

class id references id_class

and I want to select the data that is not in the lists table. I tried this:

  SELECT c.name FROM classes c JOIN lists l ON (l.id_class=c.id) WHERE l.id_class!=c.id

But no result, guess it's not correct Any solutions ?

Try `not in` or `not exists`. – Gordon Linoff Apr 01 '16 at 19:02 — Gordon Linoff, Apr 01 '16 at 19:02

score 1 · Accepted Answer · answered Apr 01 '16 at 19:02

You need a LEFT JOIN:

SELECT c.id, c.name
from classes c 
left join lists l on (l.id_class=c.id) 
where l.id_class is null

or, you can also use NOT EXISTS:

select c.id, c.name
from classes c 
where not exists (select *
                  from lists l
                  where l.id_class=c.id)

1 Answers1